Thread: do postgresql this job for me ? (firebird user)
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 ... -- View this message in context: http://www.nabble.com/do-postgresql-this-job-for-me---%28firebird-user%29-tp23690611p23690611.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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. -- Craig Ringer
>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 ? -- View this message in context: http://www.nabble.com/do-postgresql-this-job-for-me---%28firebird-user%29-tp23690611p23691966.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Le dimanche 24 mai 2009 à 11:34:46, mnavahan a écrit : > [...] > >>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 (http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html) on a regular table. For temporary objects (like table), you can also use the temp_tablespace parameter (http://www.postgresql.org/docs/8.3/interactive/runtime-config- client.html#GUC-TEMP-TABLESPACES). -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
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 ) -- View this message in context: http://www.nabble.com/do-postgresql-this-job-for-me---%28firebird-user%29-tp23690611p23694068.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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 -- View this message in context: http://www.nabble.com/do-postgresql-this-job-for-me---%28firebird-user%29-tp23690611p23694078.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Le dimanche 24 mai 2009 à 16:12:29, mnavahan a écrit : > 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 ) > Nope. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
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. -- Craig Ringer
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
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
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;
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 --- the system won't let you drop such indexes. Plus there
are obvious opportunities for human error. So we oughta do REINDEX
CONCURRENTLY someday ...
regards, tom lane
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 ]------+-------------------- locktype | relation database | 46220 relation | 71963 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 1/8976 pid | 19189 mode | AccessExclusiveLock granted | t where in this case: bs=# select relkind from pg_class where oid = 71963; relkind --------- i (1 row) we can see that the lock target is an index - specifically, the index I just DROPped. 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. -- Craig Ringer
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 --- the comment therein defending the reasoning seems out of date --- but it definitely does.) Whether it did lock the table or not, though, the index lock would certainly interfere with queries trying to use that index, or even just considering using the index during planning. So the recommended method (CREATE new index, DROP old, RENAME) is designed to hold lock on the old index for as short a time as possible. regards, tom lane
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 --- the comment therein defending the reasoning seems out of date > --- but it definitely does.) I just re-checked, and indeed it does. I could've sworn my previous test only took a lock out on the index. Thanks for the catch. -- Craig Ringer