Re: [GENERAL] I can't drop a user if I don't drop his grants beforehand??????????????????
Alvaro Herrera ha scritto: Nico Sabbi wrote: Hi, i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? This is really _crazy_ in my opinion. I'm not speaking of object ownership, but of GRANTs. As Tom says, it's a known limitation. Did you try REASSIGN OWNED and/or DROP OWNED? Hi, sorry for the big delay. No, I didn't because the tables weren't owned by the user I wanted to drop, but by another one. -- 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] I can't drop a user if I don't drop his grants beforehand??????????????????
Tom Lane ha scritto: Nico Sabbi writes: i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? Yeah, it's a known limitation. The reason it's not implemented is that some of the privileges may be in other databases besides the particular one you're connected to, and the backend hasn't got any way to get at those databases to remove the entries. Maybe someday it'll get fixed, but don't hold your breath. regards, tom lane Thanks. Unfortunately role_usage_grants is emtpy and usage_privileges doesn't contain what I expected to find, so I had to iterate over the distinct table_schema in iformation_schema.tables to drop the schema_usage grants. Nico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I can't drop a user if I don't drop his grants beforehand??????????????????
Hi, i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? This is really _crazy_ in my opinion. I'm not speaking of object ownership, but of GRANTs. Why? -- 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] case dumbiness in return from functions
Tom Lane ha scritto: Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called "REF" why should Postgres call it "ref" in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. As for portability: it may not be a concern when you have at disposal the best DB around :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Inconsistence in transaction isolation docs
Albe Laurenz ha scritto:
Nico Sabbi wrote:
/From:
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html
"
Read Committed/ [...]
to me the above sentence sounds inconsistent: it's
asserting that both 1) and 2) apply:
1) it never sees ... changes committed during query
execution by concurrent transactions
2) Notice that two successive SELECT commands can see
different data, even though they are within a single
transaction, if other transactions commit changes
during execution of the first SELECT
Can anyone explain, please?
1) means: as long as the first SELECT runs ("during
query execution"), you won't see changes made by
another transaction.
2) means: when you run a second SELECT, that SELECT
will see changes made by other transactions, even if
both SELECTs are in one (read commited) transaction.
That doesn't sound contradictory to me.
There is a difference between "during query execution"
and "within a single transaction", maybe that is where
your problem comes from.
Yours,
Laurenz Albe
well, I know how read committed behaves, but
I don't see why should anyone expect an update of the resultset
of the currently executing query after a commit by a different
transaction.
Thanks everybody who replied,
Nico
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Inconsistence in transaction isolation docs
/From: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html " Read Committed/ is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run. Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT. " to me the above sentence sounds inconsistent: it's asserting that both 1) and 2) apply: 1) it never sees ... changes committed during query execution by concurrent transactions 2) Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT Can anyone explain, please? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ORDER BY - problem with NULL values
Stefan Schwarzer ha scritto: From 8.3 beta release notes: - ORDER BY ... NULLS FIRST/LAST I think this is what you want right? Yes, indeed. Sounds great. unfortunately I am on 8.1. And wouldn't really want to migrate to 8.3 and beta for the moment order by 1 ? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] row->ARRAY or row->table casting?
Gregory Stark ha scritto: "Nico Sabbi" <[EMAIL PROTECTED]> writes: nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ERROR: missing FROM-clause entry for table "r" LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ^ I tried many variations (including casting x as tab1) obtaining only syntax errors. r.a would be the column "a" in the table named "r", but the only table in the FROM list is "x". So you have to use a workaround to make it clear to the parser that you're referring to the column "r", it would look like SELECT (r).a from (select row(tab1.*)::tab1 as r from tab1)x; yes, it works. Thanks a lot, Nico ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] row->ARRAY or row->table casting?
Tom Lane ha scritto: Nico Sabbi <[EMAIL PROTECTED]> writes: is there any way to cast a generic row to an array or to a table type? "row(...)::composite_type" should work in 8.2 and up. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I'm using 8.2.4. What's the right syntax for a case like this? nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ERROR: missing FROM-clause entry for table "r" LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ^ I tried many variations (including casting x as tab1) obtaining only syntax errors. Thanks, Nico ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] row->ARRAY or row->table casting?
Hi, is there any way to cast a generic row to an array or to a table type? The example is trivial, but it explains what I'm trying to do: nb1=# select * from tab1; a | t ---+--- 1 | a 2 | b 3 | c (3 rows) nb1=# select r from (select row(tab1.*) as r from tab1)x; r --- (1,a) (2,b) (3,c) (3 rows) nb1=# select r[1] from (select row(tab1.*) as r from tab1)x; ERROR: cannot subscript type record because it is not an array nb1=# select (r::tab1).a from (select row(tab1.*) as r from tab1)x; ERROR: cannot cast type record to tab1 The real use case is much more complex than this example of course :) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Can't SELECT from (INSERT ... RETURNING)
I thought I could use the output of INSERT...RETURNING as a set of tuples for a subquery, but it seems it's not the case: nb1=# select * from (insert into m(a) values(112) returning a); ERROR: syntax error at or near "into" LINE 1: select * from (insert into m(a) values(112) returni... ^ Is this a bug or it's not even supposed to work in theory? Such a feature would be extremely useful to have. P.S. I know it's non-portable, but this is not a problem. Thanks, Nico ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] REQUEST: option to auto-generate new sequences with CREATE TABLE (LIKE)
Hi, as the subjects reads I searched in the docs a way to instruct postgres to create new sequences when copying tables containing serial columns, but the resulting serial fields in the new tables reference the original sequence. Yes, there are workarounds, but having an option to make postgres automatically generate a new sequence for every serial field would be splendid. I hope you will consider this feature for one of the future versions of Postgres. Thanks, Nico ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Some problem with warm standby server
Simon Riggs wrote: then I updated the master with a batch of inserts, but after a while the slave stopped with these messages: LOG: restored log file "00010021" from archive LOG: record with zero length at 0/2148 LOG: invalid primary checkpoint record LOG: restored log file "00010020" from archive LOG: restored log file "00010021" from archive LOG: invalid resource manager ID in secondary checkpoint record PANIC: could not locate a valid checkpoint record LOG: startup process (PID 19619) was terminated by signal 6 LOG: aborting startup due to startup process failure Please run pg_controldata to print out the control file. Hi, sorry for the long delay. First of all I had to stop postgres with pg_ctl stop -s immediate, or it wouldn't die because of the ongoing replication. This is the output of pg_controldata: [EMAIL PROTECTED]:/usr/local/postgres_replica/data$ pg_controldata /usr/local/postgres_replica/data/ pg_control version number:812 Catalog version number: 200510211 Database system identifier: 5001030714849737714 Database cluster state: in recovery pg_control last modified: Fri 27 Apr 2007 13:20:46 CEST Current log file ID: 0 Next log file segment:26 Latest checkpoint location: 0/190C7E04 Prior checkpoint location:0/190C7DC0 Latest checkpoint's REDO location:0/190C7E04 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 3698809 Latest checkpoint's NextOID: 68745 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:Fri 27 Apr 2007 11:53:47 CEST Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: C LC_CTYPE: C Backup all the files in case we need to inspect them. ok What was the ending log sequence number (e.g. x/) from the previous recovery? I'll see if I can re-create this. judging from the logs I gues it is 0/190C7E04: LOG: restored log file "00010019.000C7E04.backup" from archive LOG: restored log file "00010019" from archive LOG: checkpoint record is at 0/190C7E04 LOG: redo record is at 0/190C7E04; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 3698809; next OID: 68745 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: automatic recovery in progress LOG: redo starts at 0/190C7E48 What did I do wrong? Is there any other procedure to follow to restart a stopped replication? You're right, using the trigger is not the right way to stop/start the standby. Just stop/start the standby server normally. as above: a plain stop hangs The trigger means that you'd like to perform a failover. There is a patch not yet applied which will make a new version of pg_standby. pg_standby's official status right now is beta, so please expect, look for and report any issues you find. Thanks. thank you ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Some problem with warm standby server
Hi, I have some doubts regarding the settings and the access procedure of warm standby servers: - can autovacuum be safely enabled on the replicator? - I'm using pg_standby (from cvs) that is generally working well as expected (logs are copied with scp); today I wanted to temporarily stop the replication to verify some data to restart it later on, so I touched the trigger file, waited for the log to report "database ready", verified that the databases were actually up-to-date. All was fine, then I ran rm -f pg_xlog/* pg_xlog/archive_status/* mv recovery.done recovery.conf (the permissions were right) /etc/init.d/postgresql stop ; /etc/init.d/postgresql start the replication seemed to start: --- LOG: database system was shut down at 2007-04-27 12:16:13 CEST LOG: starting archive recovery LOG: restore_command = "/usr/local/bin/pg_standby -s 5 -w 0 -t /usr/local/postgres_replica/trigger /usr/local/postgres_replica/log/ %f %p" cp: cannot stat `/usr/local/postgres_replica/log//0001.history': No such file or directory cp: cannot stat `/usr/local/postgres_replica/log//0001.history': No such file or directory cp: cannot stat `/usr/local/postgres_replica/log//0001.history': No such file or directory then I updated the master with a batch of inserts, but after a while the slave stopped with these messages: LOG: restored log file "00010021" from archive LOG: record with zero length at 0/2148 LOG: invalid primary checkpoint record LOG: restored log file "00010020" from archive LOG: restored log file "00010021" from archive LOG: invalid resource manager ID in secondary checkpoint record PANIC: could not locate a valid checkpoint record LOG: startup process (PID 19619) was terminated by signal 6 LOG: aborting startup due to startup process failure What did I do wrong? Is there any other procedure to follow to restart a stopped replication? Thanks, Nico ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help setting up warm standby replication
Nico Sabbi wrote: Merlin Moncure wrote: try link mode, not copy mode (-l). make sure you read about the -k switch. merlin replaced -c with -l, but the result was the same. I assume that with -k you mean the socket dir for postgres - since pg_standby doesn't seek to recognize that switch - but I didn't need it: I connected to -p 5433. Anyway, after having stopped the replication I had the same problem; in order to make the slave start I had to manually copy the log files from the archive to pg_xlog/ . Is pg_standby supposed to work with Postgres 8.2 ? or only with 8.1.? update: touching the right trigger makes the replication stop, thus I can connect to the server and see the updated data. Thanks a lot, Nico ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Help setting up warm standby replication
Merlin Moncure wrote: try link mode, not copy mode (-l). make sure you read about the -k switch. merlin replaced -c with -l, but the result was the same. I assume that with -k you mean the socket dir for postgres - since pg_standby doesn't seek to recognize that switch - but I didn't need it: I connected to -p 5433. Anyway, after having stopped the replication I had the same problem; in order to make the slave start I had to manually copy the log files from the archive to pg_xlog/ . Is pg_standby supposed to work with Postgres 8.2 ? or only with 8.1.? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Help setting up warm standby replication
Merlin Moncure wrote: On 4/16/07, Nico Sabbi <[EMAIL PROTECTED]> wrote: Is there any parameter that I have to pass to the second server to keep on requesting WALs? I still don't understand what instructs the server to continously request the master's logs. google pg_standby. I've set it up and it works. merlin Hi, I installed the second version of pg_standby.tar that you posted here. In recovery.conf I set restore_command = '/usr/local/bin/pg_standby -d -c -s 5 -w 0 /tmp/pg/ %f %p' and I see in the log file that something goes on after every update: Trigger file : (null) Waiting for WAL file : /tmp/pg//00010021 WAL file path: 00010021 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : cp /tmp/pg//00010021 pg_xlog/RECOVERYXLOG running restore : success LOG: restored file "00010021" from archivio but "cp /tmp/pg//00010021 pg_xlog/RECOVERYXLOG" looks suspicious to me. Is it expected? should WAL files overwrite pg_xlog/RECOVERYXLOG ? If I stop the replication and start the slave after having removed the recovery.conf the server doesn't come up: LOG: aborting startup due to startup process failure LOG: database system was interrupted while in recovery at log time 2007-04-16 15:29:42 CEST HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. LOG: impossibile aprire il file "pg_xlog/00010009" (file registro 0, segmento 9): No such file or directory LOG: invalid primary checkpoint record LOG: impossibile aprire il file "pg_xlog/00010021" (file registro 0, segmento 33): No such file or directory LOG: invalid secondary checkpoint record PANIC: impossibile localizzare un checkpoint record valido LOG: startup process (PID 10824) was terminated by signal 6 LOG: aborting startup due to startup process failure infact pg_xlog/ of the slave contains only RECOVERHISTORY: cat pg_xlog/RECOVERYHISTORY START WAL LOCATION: 0/920 (file 00010009) STOP WAL LOCATION: 0/A00 (file 0001000A) CHECKPOINT LOCATION: 0/920 START TIME: 2007-04-16 15:29:42 CEST LABEL: label STOP TIME: 2007-04-16 15:31:39 CEST Did I do something wrong? Thanks for your help, Nico ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help setting up warm standby replication
Tom Lane wrote: Nico Sabbi <[EMAIL PROTECTED]> writes: To begin with I followed the example of the docs: in the recovery.conf file of the slave instance I set restore_command = 'cp -av /var/lib/pgsql/data/pg_xlog/%f %p' Hm, it looks like you are trying to copy xlog segments straight from the pg_xlog directory of the master? That's not going to work, because the master will rename/overwrite those files as soon as it thinks it's done with them. You need to have an archive_command on the master that is really truly copying the data to somewhere else, and then the restore_command should copy from the somewhere else. after having set up shared directory (one for archive_command and one for recover_command) I could successfully start and sync the slave dbms, but after the initial recovery the file recovery.conf is renamed to recovery.done and no more updates/WAL are asked (the slave's log reads: LOG: archive recovery complete LOG: system database ready ) Is there any parameter that I have to pass to the second server to keep on requesting WALs? I still don't understand what instructs the server to continously request the master's logs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Help setting up warm standby replication
Hi, after having read the documentation and the few posts in this list I tried to set up a warm standby replication between two instances of postgres running on my pc in 2 different base directories and 2 different ports. (The second one was a cp -a of the first one after having created a backup point) . I don't need a realtime update: even few minutes of lag are good enough for my needs. To begin with I followed the example of the docs: in the recovery.conf file of the slave instance I set restore_command = 'cp -av /var/lib/pgsql/data/pg_xlog/%f %p' but at restart I got these messages in the logs (some translation from italian to english by me) : LOG: system shutdown at 2007-04-13 13:02:51 CEST LOG: starting archive recovery LOG: restore_command = "cp -av /var/lib/pgsql/data/pg_xlog/%f %p" cp: impossibile fare stat di `/var/lib/pgsql/data/pg_xlog/0001.history': No such file or directory `/var/lib/pgsql/data/pg_xlog/00010007' -> `pg_xlog/RECOVERYXLOG' LOG: restored file "00010007" from archive LOG: invalid record length at 0/7000218 LOG: invalid primary record checkpoint `/var/lib/pgsql/data/pg_xlog/00010007' -> `pg_xlog/RECOVERYXLOG' LOG: restored file "00010007" from archive LOG: invalid record length at 0/70001D0 LOG: invalid primary record checkpoint PANIC: impossible locating a valid record checkpoint LOG: startup process (PID 20250) was terminated by signal 6 LOG: aborting startup due to startup process failure same thing if I empty the pg_xlog/ dir (except archive_status/) I hope someone can explain what I did wrong. Another thing I didn't understand is the continous replication mechanism: will the slave server periodically call the recovery_command or do I have to setup a cronjob to instruct it to search updates? Sorry if these questions are stupid :) and thanks in advance. Nico ---(end of broadcast)--- TIP 6: explain analyze is your friend
