Hi
i am firebird user But now have some problem with it and chk postgresql to
migrate to it ...
1.have postgresql support online change of tables ?
for example i have over 400 table in db with many relation :
A: if i dont any user connect to table RR (but many user connect to other
tables) can in online Db i change table RR ?
B: if table A have FK to B can edit table B for example Add field ?
have any documented information for change table when online
(book,note,.....) of up ?
2. postgresql do any index corruption when memory low or same problem ?
3. postgresql support recursive call in stored procedure and in depth
recursive have good performance?
4.have any way port firebird stored procedure to postgresql ?
5.postgresql support Sql in stored procedure via sting (i have one string
contain sql can execute it and get result ?)
6.how performance of temp table (is in memory and then ultra fast ?)
many thanx ...
Re: GENERAL - do postgresql this job for me ? (firebird user) by Craig Ringer on
2009-05-24T08:12:56+00:00
mnavahan wrote:
> 1.have postgresql support online change of tables ?
> for example i have over 400 table in db with many relation :
> A: if i dont any user connect to table RR (but many user connect to other
> tables) can in online Db i change table RR ?
Yes.
> B: if table A have FK to B can edit table B for example Add field ?
Yes, though if users are active on tables A or B your ALTER command will
be delayed until their transactions commit or roll back.
> have any documented information for change table when online
> (book,note,.....) of up ?
I strongly recommend reading the whole PostgreSQL manual. It's excellent
- well written, covers the database's features and usage very well, and
will teach you a lot about SQL in general as well.
> 2. postgresql do any index corruption when memory low or same problem ?
No, nor if it runs out of disk space or if the database crashes. It's
not meant to, anyway, and is designed to avoid it happening.
There have been index corruption reports here, but most seem to boil
down to file system issues, disk issues, RAID controller problems, and
antivirus software interference.
The only thing that's likely to make things go pear-shaped is if the
postmaster is forcibly killed ( kill -9 ) while the worker children are
running, then the postmaster is re-started. This will never happen if
you properly configure the Linux virtual memory manager - in particular,
disable vm overcommit to prevent the OOM killer from running.
> 3. postgresql support recursive call in stored procedure and in depth
> recursive have good performance?
Yes to all of the above.
> 4.have any way port firebird stored procedure to postgresql ?
If firebird stored procedures are written in ordinary SQL, or a quite
SQL-like language (akin to Oracle's PL/SQL) then porting to PostgreSQL's
PL/PgSQL shouldn't be too hard.
I don't know of any automatic tools, but I've never had cause to look.
> 5.postgresql support Sql in stored procedure via sting (i have one string
> contain sql can execute it and get result ?)
Yes - see the EXECUTE command in PL/SQL.
> 6.how performance of temp table (is in memory and then ultra fast ?)
Temp tables are allocated on disk. However, they get the normal benefits
of PostgreSQL's caching, and tend to be very fast. You can put them in a
tablespace on a RAM disk or the like if you want, though.
I don't know if writes to temp tables go through the WAL or not, nor if
they're opened O-SYNC for synchronous writes.
Re: GENERAL - do postgresql this job for me ? (firebird user) by mnavahan on
2009-05-24T11:37:17+00:00
>database crashes. It's not meant
what mean in PG "databse crash" can i crash db without any hardware problem
and disk over (in not av !) ?
>>The only thing that's likely to make things go pear-shaped is if the
>>postmaster is forcibly killed ( kill -9 ) while the worker children are
>>running, then the postmaster is re-started.
for up ex. have way *online* repair this index ?
- see the EXECUTE command in PL/SQL.
>>Temp tables are allocated on disk. However, they get the normal benefits
>>of PostgreSQL's caching, and tend to be very fast. You can put them in a
>>tablespace on a RAM disk or the like if you want, though.
i read some about PG but dont see any topic about put some table to
different tablespace
really can do it ?
Re: GENERAL - do postgresql this job for me ? (firebird user) by Guillaume Lelarge on
2009-05-24T13:52:37+00:00
Le dimanche 24 mai 2009 =E0 11:34:46, mnavahan a =E9crit :
> [...]
> >>Temp tables are allocated on disk. However, they get the normal benefits
> >>of PostgreSQL's caching, and tend to be very fast. You can put them in a
> >>tablespace on a RAM disk or the like if you want, though.
>
> i read some about PG but dont see any topic about put some table to
> different tablespace
> really can do it ?
Yes, of course. You can use the ALTER TABLE statement=20
(http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html) on a=
=20
regular table.
For temporary objects (like table), you can also use the temp-tablespace=20
parameter (http://www.postgresql.org/docs/8.3/interactive/runtime-config-
client.html#GUC-TEMP-TABLESPACES).
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: GENERAL - do postgresql this job for me ? (firebird user) by mnavahan on
2009-05-24T14:16:08+00:00
now can help me for below :
what mean in PG "databse crash" can i crash db without any hardware problem
and disk over (in not av !) ?
(linux or win )
Re: GENERAL - do postgresql this job for me ? (firebird user) by mnavahan on
2009-05-24T14:16:36+00:00
thx of help
now can help me for below :
what mean in PG "databse crash" can i crash db without any hardware problem
and disk over (in not av !) ?
(linux or win )
best regards
Re: GENERAL - do postgresql this job for me ? (firebird user) by Guillaume Lelarge on
2009-05-24T14:29:38+00:00
Le dimanche 24 mai 2009 =E0 16:12:29, mnavahan a =E9crit :
> thx of help
>
> now can help me for below :
>
> what mean in PG "databse crash" can i crash db without any hardware probl=
em
> and disk over (in not av !) ?
> (linux or win )
>
Nope.
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: GENERAL - do postgresql this job for me ? (firebird user) by Craig Ringer on
2009-05-24T15:59:57+00:00
mnavahan wrote:
> now can help me for below :
>
> what mean in PG "databse crash" can i crash db without any hardware problem
> and disk over (in not av !) ?
> (linux or win )
Like any software, PostgreSQL has bugs (though once identified, they
don't seem to last long). Crash bugs are rare, though, and if you do
manage to crash the backend that's running a query, usually all that
happens is that the client loses its connection and the transaction is
rolled back.
PostgreSQL has to be pretty robust in the face of backend crashes,.
Buggy C extensions could crash a backend pretty easily. The postmaster
has to be able to identify and clean up after a crashed backend,
ensuring there's no damage to the on-disk tables, indexes, etc.
As for an online reindex: You shouldn't need to in the first place;
index corruption shouldn't happen. However, there are other reasons you
might want to reindex, like if you have certain unusual query patterns
that tend to bloat indexes. Anyway ... you can reindex a table without
affecting queries that don't touch that table. Queries that do touch the
table will be blocked. There isn't currently any REINDEX CONCURRENTLY
option, but people seem to have a few workarounds that do the job if you
really do need to rebuild an index on a live, active table.
You should really never need to shut PostgreSQL down to do admin work.
You might need to do work that locks a few tables for a while, but
you'll rarely affect other tables and unrelated queries.
Re: GENERAL - do postgresql this job for me ? (firebird user) by Tom Lane on
2009-05-24T16:34:33+00:00
Craig Ringer <craig@postnewspapers.com.au> writes:
> [ much good info snipped ]
> The only thing that's likely to make things go pear-shaped is if the
> postmaster is forcibly killed ( kill -9 ) while the worker children are
> running, then the postmaster is re-started.
Actually, even that doesn't cause problems in itself. The new
postmaster will refuse to start up until all the old child processes are
gone, but there's no risk of data corruption. The way you can seriously
break things is if you try to make the new postmaster start by removing
the lock file that ensures this behavior :-(
> I don't know if writes to temp tables go through the WAL or not, nor if
> they're opened O-SYNC for synchronous writes.
No for both.
regards, tom lane
Re: GENERAL - do postgresql this job for me ? (firebird user) by Thomas Kellerer on
2009-05-24T17:34:06+00:00
Craig Ringer wrote on 24.05.2009 17:58:
> There isn't currently any REINDEX CONCURRENTLY option
But them manual does list this option:
http://www.postgresql.org/docs/8.3/static/sql-createindex.html
"When this option is used, PostgreSQL will build the index without taking any
locks that prevent concurrent inserts, updates, or deletes on the table"
Thomas
Re: GENERAL - do postgresql this job for me ? (firebird user) by Christophe on
2009-05-24T17:53:55+00:00
On May 24, 2009, at 8:58 AM, Craig Ringer wrote:
> There isn't currently any REINDEX CONCURRENTLY
> option, but people seem to have a few workarounds that do the job if
> you
> really do need to rebuild an index on a live, active table.
It's pretty straight-forward to do:
CREATE INDEX CONCURRENTLY idx-new ON table ... ;
DROP INDEX idx;
ALTER INDEX idx-new RENAME TO idx;
Re: GENERAL - do postgresql this job for me ? (firebird user) by Tom Lane on
2009-05-24T18:22:28+00:00
Christophe <xof@thebuild.com> writes:
> On May 24, 2009, at 8:58 AM, Craig Ringer wrote:
>> There isn't currently any REINDEX CONCURRENTLY
>> option, but people seem to have a few workarounds that do the job if you
>> really do need to rebuild an index on a live, active table.
> It's pretty straight-forward to do:
> CREATE INDEX CONCURRENTLY idx-new ON table ... ;
> DROP INDEX idx;
> ALTER INDEX idx-new RENAME TO idx;
However, this doesn't work for an index that's underlying a UNIQUE or
PRIMARY KEY constraint, nor an index that's depended on by a FOREIGN KEY
reference Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: GENERAL - do postgresql this job for me ? (firebird user) by Craig Ringer on
2009-05-25T04:22:56+00:00
Thomas Kellerer wrote:
> Craig Ringer wrote on 24.05.2009 17:58:
>> There isn't currently any REINDEX CONCURRENTLY option
>
> But them manual does list this option:
>
> http://www.postgresql.org/docs/8.3/static/sql-createindex.html
>
> "When this option is used, PostgreSQL will build the index without
> taking any locks that prevent concurrent inserts, updates, or deletes on
> the table"
Correct - PostgreSQL supports CREATE INDEX CONCURRENTLY. Just not
REINDEX CONCURRENTLY. See:
http://www.postgresql.org/docs/8.3/static/sql-reindex.html
"To build the index without interfering with production you should drop
the index and reissue the CREATE INDEX CONCURRENTLY command."
The locking docs:
http://www.postgresql.org/docs/8.3/static/explicit-locking.html
don't say anything about which lock DROP INDEX takes on a relation. A
quick test shows it takes an AccessExclusiveLock on the affected index,
and appears to take no lock at all on the table:
-[ RECORD 6 ]So, you can indeed just:
BEGIN;
DROP INDEX ...
CREATE INDEX ... CONCURRENTLY
COMMIT;
without interrupting client work. I think.
That's really what I was referring to with "workarounds exist" though,
anyway.
Re: GENERAL - do postgresql this job for me ? (firebird user) by Tom Lane on
2009-05-25T14:40:35+00:00
Craig Ringer <craig@postnewspapers.com.au> writes:
> The locking docs:
> http://www.postgresql.org/docs/8.3/static/explicit-locking.html
> don't say anything about which lock DROP INDEX takes on a relation. A
> quick test shows it takes an AccessExclusiveLock on the affected index,
> and appears to take no lock at all on the table:
Not sure how you tested that, but index-drop() clearly takes
AccessExclusiveLock on the table too. (I'm not sure it really needs
to on the old index for as short a time as possible.
regards, tom lane
Re: GENERAL - do postgresql this job for me ? (firebird user) by Craig Ringer on
2009-05-26T04:09:33+00:00
Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
> Not sure how you tested that, but index-drop() clearly takes
> AccessExclusiveLock on the table too. (I'm not sure it really needs
> to