Re: [GENERAL] How to find how much postgresql use the memory?
I wonder how to find how much postgresql memory consumption
is (real, not allocated) ?
You could install the pg_buffercache contrib and
SELECT pg_size_pretty(
CAST(current_setting('block_size') AS integer) * max(bufferid)
)
FROM pg_buffercache WHERE relfilenode IS NOT NULL;
to find out how much of the shared memory in the buffer pool is
actually in use.
There is some extra shared memory that is used for other purposes,
and there is private memory of the backends (the latter could be
a significant number).
Why do you want that number?
If it is for tuning of shared_buffers, that is probably a bad way.
Even if shared_buffers is set much higher than you'd actually need
it, sooner or later all of your shared_buffers will be used, unless
your database is tiny.
Yours,
Laurenz Albe
--
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] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, maybe I did not understand - but what exactly happens when you do something like pg_restore -d databasename -L backup.toc backup.bak (bak - or what ever custom format you use) Is the version you did the backup with the same like the postgres version you want to restore to? I am not sure, but maybe there are occuring some problems ... $ less test.toc | grep ^; [...] ; Dumped from database version: 8.3.5 ; Dumped by pg_dump version: 8.3.5 [...] Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk Dennis C schrieb: Greetings: I already did some searches on the pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC error and am still not sure why my database's not restoring after upgrading the FreeBSD and select ports such as PostGreSQL. I did see something from a long time ago about altering the table, especially column names, quite possibly being a problem and I have done a lot of that, but as many backups as I've also done this past year, this' the first time I've had to use the restore again and am now not even sure how to get my database back. If this seems familiar and simple enough for anyone, please advise including which details may be useful here. Thanks, -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdYu7Va7znmSP9AwRAunPAKCliM90HkrNzQ51ectMmziqc7gTGwCeNrco VvYrDL/hbinlF9gaPUMau1o= =M8mK -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can I separate my Databases as in FireBird? Working with DataBases...
Hi! I came from SQLite, FireBird and DBISAM world. These database systems have a speciality that they stores one database in one file, or in one directory. Each of databases have separated file (or dir with files), and when I want to copy a database, I have MORE possibility to do this. First solution is the file-system based copy. Next is the embedded dump (FireBird). And the last is some SQL dump. Sometimes the customers have problems with the programs we wrote. In this case sometimes we ask them that send me the DB fully. They are using filesystem based copy. We open the DB, or the tables here simply, without special doing. Only we register the DB with the name test, and we can test the applications simply. But PGSQL have only one directory named data. All of the databases placed here. I cannot separate them. If the user send me a DB, I cannot copy it simply to anywhere, and register it. Because it have only SQL dump. Ok, I saw that PGSQL have tablespace. But: the databases need to be unique named. Previously I thought that tablespace higher object than database, and I can create more database with same name without collision if I use another tablespace for them. But when I tried to do this, I got error. So: we want to search for a simplified way to place and separate our 200 databases, and 200*8 applications without problems (to our development machine). And we wanna use them easier. If some of the customers want to resend a real db to us (for testing), we want to register easier this new db without rewrite our test context. Have anybody an experience with PGSQL and many similar, but not same DB usage? What is the easiest way to handle the question of the data transporting in the PGSQL? Thanks for your help: dd
Re: [GENERAL] accessing user table structures from SQL
Vincent Predoehl wrote: Sent: Saturday, January 17, 2009 5:33 AM Does postgresql have a system table that has the table structure of user tables, like systables and sysobjects in MS SQL Server? All the details about database objects are in the system catalogs: http://www.postgresql.org/docs/current/static/catalogs.html but, unless you need implementation specific details, most of the time you are better off with the standard SQL information schema: http://www.postgresql.org/docs/current/static/information-schema.html Yours, Laurenz Albe -- 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] Can I separate my Databases as in FireBird? Working with DataBases...
On Tue, Jan 20, 2009 at 1:29 AM, Durumdara durumd...@gmail.com wrote: Hi! I came from SQLite, FireBird and DBISAM world. These database systems have a speciality that they stores one database in one file, or in one directory. Each of databases have separated file (or dir with files), and when I want to copy a database, I have MORE possibility to do this. First solution is the file-system based copy. Next is the embedded dump (FireBird). And the last is some SQL dump. Sorry, but pgsql is built very differently from this. Each database lives within a cluster, and the cluster owns the dbs, and gives them coherence. But PGSQL have only one directory named data. All of the databases placed here. I cannot separate them. If the user send me a DB, I cannot copy it simply to anywhere, and register it. Because it have only SQL dump. Yep, that's about the size of it. Ok, I saw that PGSQL have tablespace. Those won't do what you want. Tablespaces exist as physical holding layers that dbs can be put in. They aren't part of the namespace or anything like that. They're an abstraction layer to allow for I/O tuning by putting various parts of the db or dbs onto different drive sets, etc... So: we want to search for a simplified way to place and separate our 200 databases, and 200*8 applications without problems (to our development machine). And we wanna use them easier. If some of the customers want to resend a real db to us (for testing), we want to register easier this new db without rewrite our test context. Then you are using the wrong db. That is not a strongsuit for pgsql. Have anybody an experience with PGSQL and many similar, but not same DB usage? Sure, we used a single pgsql server for development, qa, integration and customer test labs at my last shop. Before it got migrated to oracle for non-performance reasons. Building a customer db in the setup was pretty easy, but the tools to do it with are pg_dump / createdb / psql / pg_restore. What is the easiest way to handle the question of the data transporting in the PGSQL? You have pg_dump which allows you to transport the data between various architectures and setups, and things like Point in Time Recovery which is aimed more at backups of an active database within a well defined environment. There are some other tools to make loading lots of data easier. pg_loader and pg_bulkload. pg_loader is like oracles sqlldr and pg_bulkload is kinda a compromise between the whole copying the files method you're using with firebird and postgresql's need to have coherent data structures across all databases within a cluster. -- 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] Can I separate my Databases as in FireBird? Working with DataBases...
http://www.postgresql.org/docs/8.3/interactive/sql-createtablespace.html you probably look for that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovering from database corruption using WAL-logs
Hi! Last autumn we discovered a case of database corruption in our databases (missing rows with foreign keys pointed at them). At the time we ran version PostgreSQL 8.3.1. We upgraded our postgres to 8.3.4, but somehow the restoration from backups got forgotten. Early this year things where remembered, allthough the backups of the base from before the corruption was rotated out, and is now lost. We do however have a backup from the night before we first discovered the corruption, but we are unsure about its state (we do not know if the database was corrupted at the time of this backup). We do however, out of sheer luck (maybe), have all our WAL-logs from may 2008 to the present. So we figured we'd give WAL-replay a shot at the backup mention earlier, following the documentation (24.3.3). And it seemed to work for a while, replaying a good lot of logs but it stops at the same file, everytime, with this message: LOG: restored log file 000100EB00A1 from archive LOG: invalid contrecord length 4674 in log file 235, segment 161, offset 8192 LOG: redo done at EB/A1001AB4 LOG: last completed transaction was at log time 2008-09-29 20:12:05.551693+02» LOG: restored log file 000100EB00A1 from archive scp: /home/pgbackup/merged/0002.history: No such file or directory The file in question is dated the day after we discovered the corruption, and its not the last in that timeline (we only have one timeline). The WAL-log shows no external signs of brokenness in my eyes, as its the same size as the rest and created at the same interval. We can provide this file, if it would help figure out whats wrong in any way. We also tried setting the recovery_target to a time before the last completed transaction time, and various other target times right up to right after the time of the backup, but it still tries to play all the files, and fails on the same one. At this time we are quite stuck on this problem, so we're really hoping for some insight into this, even though its our own fault for not managing to restore an even earlier backup and replaying from that. As mentioned, we'd be happy to provide any more information that might help us recover our database. Sincerly, Kristian Klette -- Kristian Klette «Programs for sale: Fast, Reliable, Cheap: choose two.» -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovering from database corruption using WAL-logs
Hi! Last autumn we discovered a case of database corruption in our databases (missing rows with foreign keys pointed at them). At the time we ran version PostgreSQL 8.3.1. We upgraded our postgres to 8.3.4, but somehow the restoration from backups got forgotten. Early this year things where remembered, allthough the backups of the base from before the corruption was rotated out, and is now lost. We do however have a backup from the night before we first discovered the corruption, but we are unsure about its state (we do not know if the database was corrupted at the time of this backup). We do however, out of sheer luck (maybe), have all our WAL-logs from may 2008 to the present. So we figured we'd give WAL-replay a shot at the backup mention earlier, following the documentation (24.3.3). And it seemed to work for a while, replaying a good lot of logs but it stops at the same file, everytime, with this message: LOG: restored log file 000100EB00A1 from archive LOG: invalid contrecord length 4674 in log file 235, segment 161, offset 8192 LOG: redo done at EB/A1001AB4 LOG: last completed transaction was at log time 2008-09-29 20:12:05.551693+02» LOG: restored log file 000100EB00A1 from archive scp: /home/pgbackup/merged/0002.history: No such file or directory The file in question is dated the day after we discovered the corruption, and its not the last in that timeline (we only have one timeline). The WAL-log shows no external signs of brokenness in my eyes, as its the same size as the rest and created at the same interval. We can provide this file, if it would help figure out whats wrong in any way. We also tried setting the recovery_target to a time before the last completed transaction time, and various other target times right up to right after the time of the backup, but it still tries to play all the files, and fails on the same one. At this time we are quite stuck on this problem, so we're really hoping for some insight into this, even though its our own fault for not managing to restore an even earlier backup and replaying from that. As mentioned, we'd be happy to provide any more information that might help us recover our database. Sincerly, Kristian Klette -- Kristian Klette «Programs for sale: Fast, Reliable, Cheap: choose two.» -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi everybody, I have an automated mechanism to restore a demo database each night with an SQL dump. What I do inbetween a shell script is the following: 1. all database access is canceled 2. dropdb 3. createdb 4. import SQL dump: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP The last step is the issue. The shell script is run by an cronjob and if one of the steps is failing, the crondaemon sends an E-Mail. The cluster allready exists (for sure) and the language plpgsl also. The last point (4.) always creates an error (what is clear), allthough the dump is imported anyway: ERROR: Language »plpgsql« allready exists psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP || echo Der dump konnte nicht eingespielt werden. 2 And because the ERROR message is the output, the crondaemon sends an email. Question: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. The dump is created on another server using /usr/bin/pg_dump -Ft -t sys_language garfield sys_language.tar Thanks for any advice Andy - -- Andreas Wenk St.Pauli - Hamburg - Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdaQsVa7znmSP9AwRAlE0AKCVTqsD9X8nMtGHcTsfzHVElK5ePQCeMC3c vqOqVcx0ns26Nf8esi4xp/A= =zDAD -END PGP SIGNATURE- -- 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] import sql dump with psql - language creation throws error
On Tue, Jan 20, 2009 at 11:15:08AM +0100, Andreas Wenk wrote: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. instead of createdb use: createdb -D template0 Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi depesz, thanks a lot for the reply. I think that will not work, because this is (still) a 8.1 and tsearch2 is integreated. That means I have to use template1 otherwise tsearch2 would not be there ... Am I right? P.S.: upgrade to 8.3 is planned asap ;-) - -- St.Pauli - Hamburg - Germany Andreas Wenk hubert depesz lubaczewski schrieb: On Tue, Jan 20, 2009 at 11:15:08AM +0100, Andreas Wenk wrote: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. instead of createdb use: createdb -D template0 Best regards, depesz -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdacrVa7znmSP9AwRAlZ/AJoDlznuIlPI/ODu9HSQfSIXlKc9FgCgjdmo 6/WJlfiUo0pNjwpaoS9XIok= =3AQA -END PGP SIGNATURE- -- 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] import sql dump with psql - language creation throws error
try creating whole db from scratch, do the dump with option -c (will recreate all objects automagically). you can also issue 'drop language plpgsql [cascade]' before. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Get object creation sql script in psql client
Is there a way to get i.e. table creation sql script from an existing table in psql (not postgresql, but psql client), like it is in pgAdmin? I.e. i point it to existing table 'foo', and it writes: CREATE TABLE foo ( bar int ); -- 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] Get object creation sql script in psql client
pg_dump -t foo database -- 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] Get object creation sql script in psql client
Grzegorz Jaśkiewicz wrote: pg_dump -t foo database Thanks, but pg_dump is not psql client (i meant the */bin/psql interactive shell), and there is only an option for table objects, and no one for i.e. indices. -- 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] Get object creation sql script in psql client
pg_dump -t ANYOBJECT database afaik., try it - play with it. -- GJ -- 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] Get object creation sql script in psql client
Grzegorz Jaśkiewicz wrote: pg_dump -t ANYOBJECT database afaik., try it - play with it. that does not work for indices. But the index creation is shown when placing it's parent table into -t. Thanks for the help, Grzegorz, the issue is solved. -- 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] How can I look at a recursive table dependency tree?
Richard Huxton wrote: Igor Katson wrote: I want to DROP CASCADE a table, but I am afraid that amoung numerous recursive dependencies there will be smth, that I don't want to drop. Is there a way to watch all dependencies recursively without doing a drop? BEGIN; DROP CASCADE... -- check things ROLLBACK; Thanks everyone, who responded. DROP TABLE while in transaction (if you just want to look at the cascading drops) is really fast and safe. -- 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] Get object creation sql script in psql client
try exhausting possibilities there. Many ppl don't know that you can have multiple -t objects , and than use -T for stuff you don't want as well. It does the job pretty often I have to say. -- 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] Get object creation sql script in psql client
In response to Igor Katson : Is there a way to get i.e. table creation sql script from an existing table in psql (not postgresql, but psql client), like it is in pgAdmin? I.e. i point it to existing table 'foo', and it writes: CREATE TABLE foo ( bar int ); wait for 8.4: http://developer.postgresql.org/pgdocs/postgres/functions-info.html - pg_get_functiondef(func_oid) - pg_get_indexdef(index_oid) - pg_get_ruledef(rule_oid) - pg_get_triggerdef(trigger_oid) - pg_get_viewdef(view_name) Unfortunately, i can't see such a function for tabledef. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] Autovacuum daemon terminated by signal 11
Tom Lane wrote: Anyway, it happens consistently on my HP box. I find that your proposed patch fixes it, but makes the normal path crash :-( --- the loop in do_autovacuum has to be executed in AutovacMemCxt, because it creates an Oid List that gets passed to vacuum() and had better not be in a transaction-lifetime context. The attached modified patch works for me. Committed this patch to both 8.1 and 8.2. Thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] conexiones ssl
Henry Interiano wrote: Hola a todos necesito ayuda como configurar mi base de datos como aceptar conexiones ssl desde cualquier ip, mi base de datos esta instalada en Windows: Hola Henry, La lista pgsql-general es en inglés. Por favor usa pgsql-es-ayuda para hacer preguntas (y obtener respuestas) en castellano. Gracias, -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Can I separate my Databases as in FireBird? Working with DataBases...
Durumdara wrote: I came from SQLite, FireBird and DBISAM world. These database systems have a speciality that they stores one database in one file, or in one directory. Each of databases have separated file (or dir with files), and when I want to copy a database, I have MORE possibility to do this. First solution is the file-system based copy. Next is the embedded dump (FireBird). And the last is some SQL dump. Sometimes the customers have problems with the programs we wrote. In this case sometimes we ask them that send me the DB fully. They are using filesystem based copy. We open the DB, or the tables here simply, without special doing. Only we register the DB with the name test, and we can test the applications simply. But PGSQL have only one directory named data. All of the databases placed here. I cannot separate them. If the user send me a DB, I cannot copy it simply to anywhere, and register it. Because it have only SQL dump. Ok, here I start having trouble following. There are two ways to backup a PostgreSQL database: 1) Stop the server, copy everything in the database directory. This can be restored on a computer with the same OS + hardware + address size. 2) Dump the database with pg_dump. This can be restored on any PostgreSQL installation with the same version. After you restore the backup or dump, you can connect to the cluster and simply rename the database to test if you want. Ok, I saw that PGSQL have tablespace. Yes, but you cannot simply copy the tablespace to copy the database. Many metadata live outside this directory. But: the databases need to be unique named. Previously I thought that tablespace higher object than database, and I can create more database with same name without collision if I use another tablespace for them. But when I tried to do this, I got error. Yes, the tablespace is not part of the database name. It just says where tables will be stored. So: we want to search for a simplified way to place and separate our 200 databases, and 200*8 applications without problems (to our development machine). And we wanna use them easier. If some of the customers want to resend a real db to us (for testing), we want to register easier this new db without rewrite our test context. Have anybody an experience with PGSQL and many similar, but not same DB usage? What is the easiest way to handle the question of the data transporting in the PGSQL? I would teach the customer how to make a pg_dump of the database and send that. You can then restore it into any PostgreSQL cluster and rename the database to whatever you want. Yours, Laurenz Albe -- 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] CREATE parametric partial INDEX within a function body
On Monday 19 January 2009 23:28:08 Reg Me Please wrote: On Monday 19 January 2009 22:49:17 Gerhard Heift wrote: On Mon, Jan 19, 2009 at 08:19:06PM +0100, Reg Me Please wrote: Hi all. I have a maintenance PL/pgSQL function that needs to recreate a partial index (not a REINDEX, though). In the WHERE condition of the index I have one of the function arguments. A plain CREATE INDEX ... WHERE ... will lead to a runtime error like this: tmp2=# SELECT * FROM f_maint1( '20080401'::timestamptz ); ERROR: there is no parameter $1 CONTEXT: SQL statement CREATE INDEX i_special_part ON t_atable( col1,col2 ) WHERE col3 = $1 PL/pgSQL function f_maint1 line 28 at SQL statement To work this issue around I switched to dynamic SQL with EXECUTE 'CREATE INDEX ...' What about EXECUTE 'CREATE INDEX bla ON t_table WHERE (col = ' || pg_catalog.quote_literal($1) || ')'; Regards, Gerhard Exactly what I did: To work this issue around I switched to dynamic SQL with EXECUTE 'CREATE INDEX ...' The question remains, though. Is that a bug or is the documentation needing improvements? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database schema data synchronizer software for PostgreSQL?
Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Thank you, -- Best Regards, Csaba Együd -- 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] import sql dump with psql - language creation throws error
On Tuesday 20 January 2009 2:15:08 am Andreas Wenk wrote: Hi everybody, I have an automated mechanism to restore a demo database each night with an SQL dump. What I do inbetween a shell script is the following: 1. all database access is canceled 2. dropdb 3. createdb 4. import SQL dump: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP The last step is the issue. The shell script is run by an cronjob and if one of the steps is failing, the crondaemon sends an E-Mail. The cluster allready exists (for sure) and the language plpgsl also. The last point (4.) always creates an error (what is clear), allthough the dump is imported anyway: ERROR: Language »plpgsql« allready exists psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP || echo Der dump konnte nicht eingespielt werden. 2 And because the ERROR message is the output, the crondaemon sends an email. Question: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. The dump is created on another server using /usr/bin/pg_dump -Ft -t sys_language garfield sys_language.tar Could be lack of coffee, but I am somewhat confused. Is this indeed the dump file you are restoring? I didn't think you could restore a tar dump with psql. Also you are only dumping one table, so I am not sure why the whole db is being recreated. Thanks for any advice Andy -- Andreas Wenk St.Pauli - Hamburg - Germany -- Adrian Klaver akla...@comcast.net -- 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] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Adrian Klaver schrieb: On Tuesday 20 January 2009 2:15:08 am Andreas Wenk wrote: Hi everybody, I have an automated mechanism to restore a demo database each night with an SQL dump. What I do inbetween a shell script is the following: 1. all database access is canceled 2. dropdb 3. createdb 4. import SQL dump: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP The last step is the issue. The shell script is run by an cronjob and if one of the steps is failing, the crondaemon sends an E-Mail. The cluster allready exists (for sure) and the language plpgsl also. The last point (4.) always creates an error (what is clear), allthough the dump is imported anyway: ERROR: Language »plpgsql« allready exists psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP || echo Der dump konnte nicht eingespielt werden. 2 And because the ERROR message is the output, the crondaemon sends an email. Question: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. The dump is created on another server using /usr/bin/pg_dump -Ft -t sys_language garfield sys_language.tar Could be lack of coffee, but I am somewhat confused. Is this indeed the dump file you are restoring? I didn't think you could restore a tar dump with psql. Also you are only dumping one table, so I am not sure why the whole db is being recreated. Adrian, no lack of coffee but my fault. You are totally right - that was a copy and paste error. For sure the dump is *.sql. Until now there is no onboard solution for this issue. Means, the import of the dump is working correct but a message is thrown: FEHLER: Sprache »plpgsql« existiert bereits (means ERROR: Language »plpgsql« allready exists). What I do now - and this is not really beautiful - is to erase that message before oputput is created from the shell script: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP 21 | grep -v FEHLER: Sprache »plpgsql« existiert bereits exit 0 Now, no output from the script is catched by the crondaemon and no email is sent. On the other hand, no error from the above line is catched at all ... unfortunately ... By the way - language plpgsql was created with createlang plpgsql template1. And because of tsearch2 it's not possible to create the db from template0. Got another idea? Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk Thanks for any advice Andy -- Andreas Wenk St.Pauli - Hamburg - Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdegzVa7znmSP9AwRAvtbAKDNv9O3HyEe7kn1fjQpPIMRtk9PPwCfRf5a KkatRS9OojoHBXGxQA05gKY= =qhRy -END PGP SIGNATURE- -- 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] import sql dump with psql - language creation throws error
On Tuesday 20 January 2009 7:05:23 am Andreas Wenk wrote: Adrian, no lack of coffee but my fault. You are totally right - that was a copy and paste error. For sure the dump is *.sql. Until now there is no onboard solution for this issue. Means, the import of the dump is working correct but a message is thrown: FEHLER: Sprache »plpgsql« existiert bereits (means ERROR: Language »plpgsql« allready exists). What I do now - and this is not really beautiful - is to erase that message before oputput is created from the shell script: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP 21 | grep -v FEHLER: Sprache »plpgsql« existiert bereits exit 0 Now, no output from the script is catched by the crondaemon and no email is sent. On the other hand, no error from the above line is catched at all ... unfortunately ... By the way - language plpgsql was created with createlang plpgsql template1. And because of tsearch2 it's not possible to create the db from template0. Got another idea? Cheers Andy Just to point you to Grzegorz's suggestion of using the -c switch in the pg_dump command. To quote the manual: -c Output commands to clean (drop) database objects prior to (the commands for) creating them. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. Also I am not sure cron sending the email is a bad thing. Serves as indicator that the process ran. -- Adrian Klaver akla...@comcast.net -- 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] left join with smaller table or index on (XXX is not null) to avoid upsert
the only difference here is, that the trigger will memcmp (compare) all data. Say, if we have two columns, int and bytea, and just want to compare fist one - it will use a lot of cpu in vain. I have to say, it is a shame sometimes - that trigger isn't aware of what fields we do update exactly -- 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] Database schema data synchronizer software for PostgreSQL?
On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Yes, you should definitely do that and store the scripts to do it in your source code management system along with all the rest of the deploy and upgrade scripts. They can't be generated automatically either. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Adrian Klaver schrieb: On Tuesday 20 January 2009 7:05:23 am Andreas Wenk wrote: Adrian, no lack of coffee but my fault. You are totally right - that was a copy and paste error. For sure the dump is *.sql. Until now there is no onboard solution for this issue. Means, the import of the dump is working correct but a message is thrown: FEHLER: Sprache »plpgsql« existiert bereits (means ERROR: Language »plpgsql« allready exists). What I do now - and this is not really beautiful - is to erase that message before oputput is created from the shell script: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP 21 | grep -v FEHLER: Sprache »plpgsql« existiert bereits exit 0 Now, no output from the script is catched by the crondaemon and no email is sent. On the other hand, no error from the above line is catched at all ... unfortunately ... By the way - language plpgsql was created with createlang plpgsql template1. And because of tsearch2 it's not possible to create the db from template0. Got another idea? Cheers Andy Just to point you to Grzegorz's suggestion of using the -c switch in the pg_dump command. To quote the manual: -c Output commands to clean (drop) database objects prior to (the commands for) creating them. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. I allready tried -c ... it's not what I need or helps - thanks anyway ;-) Also I am not sure cron sending the email is a bad thing. Serves as indicator that the process ran. Yes that's correct ... in a way - but imagine you have 20 cronjobs running - do you really want to spam your mailbox with these messages? I think the better way is to leave it running and only in case of an error inform me. I think this is the common way sysadmins are doing it ... Cheers Andy -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdfLiVa7znmSP9AwRAqYlAKCONfrcirRuDzFYYs9+1Sbg46JejgCgif0V 2RMlNbRaqK7aAomCk6tzPow= =+whp -END PGP SIGNATURE- -- 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] import sql dump with psql - language creation throws error
On Tuesday 20 January 2009 7:50:58 am Andreas Wenk wrote: Just to point you to Grzegorz's suggestion of using the -c switch in the pg_dump command. To quote the manual: -c Output commands to clean (drop) database objects prior to (the commands for) creating them. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. I allready tried -c ... it's not what I need or helps - thanks anyway ;-) I don't know enough about tsearch, but is plpgsql a dependency? If not could you use the information here, http://www.postgresql.org/docs/8.3/interactive/manage-ag-templatedbs.html to create a database template that has tsearch but not plpgsql and then create your demo db using it? Also I am not sure cron sending the email is a bad thing. Serves as indicator that the process ran. Yes that's correct ... in a way - but imagine you have 20 cronjobs running - do you really want to spam your mailbox with these messages? I think the better way is to leave it running and only in case of an error inform me. I think this is the common way sysadmins are doing it ... True but sometimes the error is that nothing happened. I have never found the no news is good news saying comforting. Making decisions on a negative can come back to bite you. My 0.02 cents. Cheers Andy -- Adrian Klaver akla...@comcast.net -- 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] left join with smaller table or index on (XXX is not null) to avoid upsert
Hi, Le lundi 19 janvier 2009, Tom Lane a écrit : But having said that: 8.4 will provide a standard trigger that short-circuits vacuous updates, which you can apply to tables in which you think vacuous updates are likely. It's your responsibility to place the trigger so that it doesn't interfere with any other trigger processing you may have. I'm preparing an 8.3 backport of it, which in fact is running just fine already now. Would pgfoundry let me chekout the module I imported earlier today the code and debian packaging would be on a public CVS already. http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/backports/min_update/ If people want to see the code before pgfoundry allows me to put it in the CVS overthere, here it is (slow server): http://pgsql.tapoueh.org/min_update Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
Greetings; And thanks for your reply! I tried the following: less xaa | grep ^; xaa may be a binary file. See it anyway? y Binary file (standard input) matches And so am not sure which version I did the following from: pg_dump -c -F c -Z 9 [databasename] But I installed it about a year ago, so whichever was the release then. Am trying to restore to the following: postgresql-client-7.4.21 PostgreSQL database (client) postgresql-plpython-7.4.21_1 A module for using Python to write SQL functions postgresql-server-7.4.21 The most advanced open-source database available anywhere As follows: cat * | pg_restore -d [databasename] And the following error's exactly what happens: pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC It's worked for me many years before, but am now concerned that I've maybe just lost this database, so any help would be so greatly appreciated and thank you again so much! On Tue, Jan 20, 2009 at 12:30 AM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, maybe I did not understand - but what exactly happens when you do something like pg_restore -d databasename -L backup.toc backup.bak (bak - or what ever custom format you use) Is the version you did the backup with the same like the postgres version you want to restore to? I am not sure, but maybe there are occuring some problems ... $ less test.toc | grep ^; [...] ; Dumped from database version: 8.3.5 ; Dumped by pg_dump version: 8.3.5 [...] Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk Dennis C schrieb: Greetings: I already did some searches on the pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC error and am still not sure why my database's not restoring after upgrading the FreeBSD and select ports such as PostGreSQL. I did see something from a long time ago about altering the table, especially column names, quite possibly being a problem and I have done a lot of that, but as many backups as I've also done this past year, this' the first time I've had to use the restore again and am now not even sure how to get my database back. If this seems familiar and simple enough for anyone, please advise including which details may be useful here. Thanks, -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdYu7Va7znmSP9AwRAunPAKCliM90HkrNzQ51ectMmziqc7gTGwCeNrco VvYrDL/hbinlF9gaPUMau1o= =M8mK -END PGP SIGNATURE-
Re: [GENERAL] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
On Tue, Jan 20, 2009 at 11:15 AM, Dennis C dcsw...@gmail.com wrote: Greetings; And thanks for your reply! I tried the following: less xaa | grep ^; xaa may be a binary file. See it anyway? y Binary file (standard input) matches And so am not sure which version I did the following from: pg_dump -c -F c -Z 9 [databasename] It's kind of important, but... PostgreSQL's dump and restore commands are designed to work from the same versions or going a new version from an older version. Going backwards is not supported. But I installed it about a year ago, so whichever was the release then. Am trying to restore to the following: 8.2 or 8.3. Unless you were using a version supplied by a distro, which could go further back. postgresql-client-7.4.21 PostgreSQL database (client) postgresql-plpython-7.4.21_1 A module for using Python to write SQL functions postgresql-server-7.4.21 The most advanced open-source database available anywhere Now's the time to upgrade. 7.4 is the oldest supported version, which means it's next for the chopping block. It's also A LOT slower than 8.3. Can you get and install a newer version of pgsql, preferably 8.3 and try restoring there? cat * | pg_restore -d [databasename] The normal way to run it is to use the -f switch for the file pg_restore -d dbname -f filename Not sure there's anything wrong with your way, but I've never used pg_restore like that. -- 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] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
OK that was it! Wow, thank you so very much! Nice to know it was just plpython tracking such an obsolete version of postgresql much to my dismay now (especially even going backwards, which didn't even occur to me), as opposed to postgresql itself being less reliable than I've come to expect over the years! Thanks for all your great work with that too in the first place! On Tue, Jan 20, 2009 at 10:40 AM, Scott Marlowe scott.marl...@gmail.comwrote: On Tue, Jan 20, 2009 at 11:15 AM, Dennis C dcsw...@gmail.com wrote: Greetings; And thanks for your reply! I tried the following: less xaa | grep ^; xaa may be a binary file. See it anyway? y Binary file (standard input) matches And so am not sure which version I did the following from: pg_dump -c -F c -Z 9 [databasename] It's kind of important, but... PostgreSQL's dump and restore commands are designed to work from the same versions or going a new version from an older version. Going backwards is not supported. But I installed it about a year ago, so whichever was the release then. Am trying to restore to the following: 8.2 or 8.3. Unless you were using a version supplied by a distro, which could go further back. postgresql-client-7.4.21 PostgreSQL database (client) postgresql-plpython-7.4.21_1 A module for using Python to write SQL functions postgresql-server-7.4.21 The most advanced open-source database available anywhere Now's the time to upgrade. 7.4 is the oldest supported version, which means it's next for the chopping block. It's also A LOT slower than 8.3. Can you get and install a newer version of pgsql, preferably 8.3 and try restoring there? cat * | pg_restore -d [databasename] The normal way to run it is to use the -f switch for the file pg_restore -d dbname -f filename Not sure there's anything wrong with your way, but I've never used pg_restore like that.
[GENERAL] Custom type, operators and operator class not sorting/indexing correctly
Dear all,
I've created a new domain (debversion) derived from TEXT, which
includes its own operators ( = = = and ), and also its
own operator class for BTREE indices.
The operators function correctly when I test them by themselves,
e.g. SELECT x y;
However, if I create a table with a column of this type, ORDER BY
does not result in correct ordering. I have to explicitly add
'USING ' to the query, and even this fails to work if I haven't
defined the operator class:
# SELECT * FROM testv ORDER BY version ASC;
version
--
1.0.3-3
3.0.7+1-1
3.0.7+1-2
3.0.7+1-2~lenny2
(4 rows)
# SELECT * FROM testv ORDER BY version USING ;
version
--
1.0.3-3
3.0.7+1-1
3.0.7+1-2~lenny2
3.0.7+1-2
(4 rows)
The latter shows the correct ordering. The former appears to be
using the lexical ordering of the TEXT type. Adding an index
does not affect the ordering, even if I explictly make it use my
operator class (it's also set as the default).
The SQL code to create the type and demonstrate the problem follows
at the end of this mail. It requires the PL/Perl and PL/pgSQL
languages to be available. It shows example queries to demonstrate
the ordering issue above.
I thought that I had correctly defined the type, functions, operators
and operator class in order for everything to function correctly, but
I must be missing some final piece of the puzzle or some PostgreSQL
subtlety I'm not aware of (this is my first attempt at defining
operators, and I am also a newcomer to using procedural languages).
Could anyone suggest what I've done wrong here?
Many thanks,
Roger Leigh
--
.''`. Roger Leigh
: :' : Debian GNU/Linux http://people.debian.org/~rleigh/
`. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/
`-GPG Public Key: 0x25BFB848 Please GPG sign your mail.
--- WannaBuild Database Schema for PostgreSQL-*- sql -*-
--- Debian version type and operators
---
--- Code derived from Dpkg::Version:
--- Copyright © Colin Watson cjwat...@debian.org
--- Copyright © Ian Jackson i...@debian.org
--- Copyright © 2007 by Don Armstrong d...@donarmstrong.com
---
--- PostgreSQL SQL, PL/pgSQL and PL/Perl:
--- Copyright © 2008 Roger Leigh rle...@debian.org
---
--- This program is free software: you can redistribute it and/or modify
--- it under the terms of the GNU General Public License as published by
--- the Free Software Foundation, either version 2 of the License, or
--- (at your option) any later version.
---
--- This program is distributed in the hope that it will be useful, but
--- WITHOUT ANY WARRANTY; without even the implied warranty of
--- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
--- General Public License for more details.
---
--- You should have received a copy of the GNU General Public License
--- along with this program. If not, see
--- http://www.gnu.org/licenses/.
SET SESSION plperl.use_strict TO 't';
CREATE DOMAIN debversion AS TEXT;
COMMENT ON DOMAIN debversion IS 'Debian package version number';
ALTER DOMAIN debversion
ADD CONSTRAINT debversion_syntax
CHECK (VALUE !~ '[^-+:.0-9a-zA-Z~]');
-- From Dpkg::Version::parseversion
CREATE OR REPLACE FUNCTION debversion_split (debversion)
RETURNS text[] AS $$
my $ver = shift;
my %verhash;
if ($ver =~ /:/)
{
$ver =~ /^(\d+):(.+)/ or die bad version number '$ver';
$verhash{epoch} = $1;
$ver = $2;
}
else
{
$verhash{epoch} = 0;
}
if ($ver =~ /(.+)-(.*)$/)
{
$verhash{version} = $1;
$verhash{revision} = $2;
}
else
{
$verhash{version} = $ver;
$verhash{revision} = 0;
}
return [$verhash{'epoch'}, $verhash{'version'}, $verhash{'revision'}];
$$
LANGUAGE plperl
IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_split (debversion)
IS 'Split debian version into epoch, upstream version and revision';
CREATE OR REPLACE FUNCTION debversion_epoch (version debversion)
RETURNS text AS $$
DECLARE
split text[];
BEGIN
split := debversion_split(version);
RETURN split[1];
END;
$$
LANGUAGE plpgsql
IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_epoch (debversion)
IS 'Get debian version epoch';
CREATE OR REPLACE FUNCTION debversion_version (version debversion)
RETURNS text AS $$
DECLARE
split text[];
BEGIN
split := debversion_split(version);
RETURN split[2];
END;
$$
LANGUAGE plpgsql
IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_version (debversion)
IS 'Get debian version upstream version';
CREATE OR REPLACE FUNCTION debversion_revision (version debversion)
RETURNS text AS $$
DECLARE
split text[];
BEGIN
split := debversion_split(version);
RETURN split[3];
END;
$$
LANGUAGE plpgsql
IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_revision (debversion)
IS 'Get debian version revision';
-- From Dpkg::Version::parseversion
CREATE OR REPLACE FUNCTION debversion_compare_single
Re: [GENERAL] How to find how much postgresql use the memory?
Ok I see. So what's the best way to find optimum value for various
memory-related setting of postgresql ?
On Tue, Jan 20, 2009 at 3:27 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:
I wonder how to find how much postgresql memory consumption
is (real, not allocated) ?
You could install the pg_buffercache contrib and
SELECT pg_size_pretty(
CAST(current_setting('block_size') AS integer) * max(bufferid)
)
FROM pg_buffercache WHERE relfilenode IS NOT NULL;
to find out how much of the shared memory in the buffer pool is
actually in use.
There is some extra shared memory that is used for other purposes,
and there is private memory of the backends (the latter could be
a significant number).
Why do you want that number?
If it is for tuning of shared_buffers, that is probably a bad way.
Even if shared_buffers is set much higher than you'd actually need
it, sooner or later all of your shared_buffers will be used, unless
your database is tiny.
Yours,
Laurenz Albe
Re: [GENERAL] Database schema data synchronizer software for PostgreSQL?
On Tuesday 20 January 2009 10:44:06 David Fetter wrote: On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. While I would agree that these tools can't solve organizational problems, they do exist: http://pgdiff.sourceforge.net/ http://apgdiff.sourceforge.net/ http://www.dbsolo.com/ http://sqlmanager.net/en/products/postgresql/dbcomparer there are others too... -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- 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] Is this on the to-do list?
On Monday 19 January 2009 18:13:51 Bruce Momjian wrote: Thomas Kellerer wrote: A B wrote on 18.01.2009 22:43: From the docs: http://www.postgresql.org/docs/8.3/interactive/sql-update.html According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select: UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id); This is not currently implemented ? the source must be a list of independent expressions. Is this feature going into postgresql any day soon? :-) It's on the TODO list: http://wiki.postgresql.org/wiki/Todo#UPDATE Also, I don't know if anyone working on this item for 8.4 or 8.5. Probably because there is a work-around... UPDATE accounts SET contact_last_name = x.last_name, set contact_first_name = x.first_name FROM (select last_name, first_name from salesmen where salesmen.id = accounts.sales_id) x Which is great if you just want to get this done, but sucks if you wanted the specific syntax from above. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- 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] Database schema data synchronizer software for PostgreSQL?
On Tue, Jan 20, 2009 at 09:51:25PM -0500, Robert Treat wrote: On Tuesday 20 January 2009 10:44:06 David Fetter wrote: On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. While I would agree that these tools can't solve organizational problems, they do exist: http://pgdiff.sourceforge.net/ http://apgdiff.sourceforge.net/ http://www.dbsolo.com/ http://sqlmanager.net/en/products/postgresql/dbcomparer there are others too... There exist tools which can transform an empty database schema into another. They fail at the real life use case of changing a schema that already has data in it because they can't tell a changed column from one that's been dropped and another added, so I stand by my original contention :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Database schema data synchronizer software for PostgreSQL?
-Original Message- From: David Fetter [mailto:da...@fetter.org] Sent: Tuesday, January 20, 2009 4:44 PM To: Csaba Együd Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Database schema data synchronizer software for PostgreSQL? On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Yes, you should definitely do that and store the scripts to do it in your source code management system along with all the rest of the deploy and upgrade scripts. They can't be generated automatically either. David, I see your points and generally can agree with, but there is a level which can be automated - I mean a mechanic comparison. Of course the result sync script must be tested before applying in production environment. These tools can/could save a lot of time. In my opinion the result, this way or that way, would be the same: a version migration or sync script to attach to the upgrade package. I think the difference is that I do not have to maintain a db script during the development to keep it up to date. I simply concentrate on the task not the administration. I may be wrong... Up to now I've been doing it the manual way but it makes me really non-effective. Thank you, -- Csaba -- 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] Database schema data synchronizer software for PostgreSQL?
-Original Message- From: Robert Treat [mailto:xzi...@users.sourceforge.net] Sent: Wednesday, January 21, 2009 3:51 AM To: pgsql-general@postgresql.org Cc: David Fetter; Csaba Együd Subject: Re: [GENERAL] Database schema data synchronizer software for PostgreSQL? On Tuesday 20 January 2009 10:44:06 David Fetter wrote: On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. While I would agree that these tools can't solve organizational problems, they do exist: http://pgdiff.sourceforge.net/ http://apgdiff.sourceforge.net/ http://www.dbsolo.com/ http://sqlmanager.net/en/products/postgresql/dbcomparer Robert, Thank you for your suggestions. I will glace at them. -- Csaba -- 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] Database schema data synchronizer software for PostgreSQL?
On Jan 20, 2009, at 11:27 PM, Együd Csaba wrote: -Original Message- From: Robert Treat [mailto:xzi...@users.sourceforge.net] Sent: Wednesday, January 21, 2009 3:51 AM To: pgsql-general@postgresql.org Cc: David Fetter; Csaba Együd Subject: Re: [GENERAL] Database schema data synchronizer software for PostgreSQL? On Tuesday 20 January 2009 10:44:06 David Fetter wrote: On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. While I would agree that these tools can't solve organizational problems, they do exist: http://pgdiff.sourceforge.net/ http://apgdiff.sourceforge.net/ http://www.dbsolo.com/ http://sqlmanager.net/en/products/postgresql/dbcomparer Robert, Thank you for your suggestions. I will glace at them. -- Csaba dbsolo does a decent job. I think they main thing here is to check for inconsistencies and see what they are, rather then a tool that takes over the administrative task. We all understand David's point of view, but it can't hurt to have a tool in place that can verify the consistency of both schema's. Ries -- 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] Database schema data synchronizer software for PostgreSQL?
dbsolo does a decent job. I think they main thing here is to check for inconsistencies and see what they are, rather then a tool that takes over the administrative task. We all understand David's point of view, but it can't hurt to have a tool in place that can verify the consistency of both schema's. Ries Ries, I meant definitely this, thanks. DB Solo is really does a nice job. I found only two minor problems in the sync script: 1. It put a create trigger... statement before it created the trigger function itself - moved to the end of the script 2. I tried to alter column a time column to timestampz which resulted in an error message. I corrected these two things and the database is OK now. I'm absolutely happy with this level of automation. Thank you all for your suggestions, --Csaba -- 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] Custom type, operators and operator class not sorting/indexing correctly
Roger Leigh rle...@codelibre.net writes: I've created a new domain (debversion) derived from TEXT, which includes its own operators ( = = = and ), and also its own operator class for BTREE indices. You can't realistically attach such things to a domain; try making a separate type, perhaps with an implicit cast to text to allow use of text operators for other purposes. Elein was going to look into tweaking the coercion rules so that functions on domains had a non-negligible chance of being useful, but we've not heard much about that project lately. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
