Re: [GENERAL] Replication options?
On 8/12/2004 12:02 PM, Joshua D. Drake wrote: To make a transaction durable, the changes first have to be recorded in PostgreSQL's crash recovery WAL. Only after that data is flushed to the disk it can be assumed that the transaction will be redone in the case of an immediately following crash. If a replication system now logs the commit event before the WAL operation happens, it is possible that the transaction does not commit on the master due to a crash, but it will be replayed and committed on the slaves. On the other side if the replication logging of the commit is done after the WAL operation, it must be assured that WAL replay during crash recovery also causes replication log journal to be recovered or repeated. In short, the replication log must be covered by the same redo mechanism the crash recovery system uses. This I will have to verify with our programmers as to exactly when the replication occurs. Joshua, you never followed up to this one. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] figuring out if there was a transaction in this connection already
Hi, I would like to be able to figure out if a table has been updated in this connection from within a C trigger. I have already tried to the use a query with currval on the autoincremented primary key but that exits the trigger with table.currval is not yet defined in this session Is there a way to trap / ignore this error? Or a way to check if there was a transaction in this connection before. Thanks for any input. Best wishes, M __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] figuring out if there was a transaction in this connection already
On Sat, Sep 25, 2004 at 07:16:19AM -0700, Kundham Saare wrote:
I would like to be able to figure out if a table has
been updated in this connection from within a C
trigger.
I have already tried to the use a query with currval
on the autoincremented primary key but that exits the
trigger with
table.currval is not yet defined in this session
Checking currval() isn't a valid test for inserts because an insert
might have been rolled back, but the sequence would still have been
incremented:
test= SELECT currval('person_id_seq');
ERROR: currval of sequence person_id_seq is not yet defined in this session
test= BEGIN;
BEGIN
test= INSERT INTO person (name) VALUES ('John Doe');
INSERT 30437 1
test= ROLLBACK;
ROLLBACK
test= SELECT currval('person_id_seq');
currval
-
12
Checking currval() also wouldn't tell you whether any rows in a
table had been updated.
Is there a way to trap / ignore this error? Or a way
to check if there was a transaction in this connection
before.
Why do you need to know this? What are you trying to do?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Modifying users password in pg_shadow from php
Hi All, I'm wonder if there is any possibility to modify a users password by updating the pg_shadow table. I'd like to ensure that when the user modifies his/her http password (htpasswd) than his database password also changes. It is important to have the same password for both services. I plan to do it from php. Is there any way to do so? Thans, -- Csaba Egyd --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.766 / Virus Database: 513 - Release Date: 2004.09.17. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] figuring out if there was a transaction in this connection already
Checking currval() also wouldn't tell you whether any rows in a table had been updated. I only need to know if there a row has been inserted. I am trying to extend dbmirror so that I can easily replicate all the transaction from a query even if there it contains multiple update statements within it. Currently, this will insert multiple rows into a pending table with different ID's. Alternatively, is there some way to figure out what the pid of the current connection is from the C trigger function? Best wishes, M __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Modifying users password in pg_shadow from php
On Sat, Sep 25, 2004 at 05:40:27PM +0200, Egy?d Csaba wrote: I'm wonder if there is any possibility to modify a users password by updating the pg_shadow table. Why not use ALTER USER? http://www.postgresql.org/docs/7.4/static/sql-alteruser.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Modifying users password in pg_shadow from php
ohh thanks. what a stupid i am. :))) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Saturday, September 25, 2004 5:56 PM To: Egy?d Csaba Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Modifying users password in pg_shadow from php On Sat, Sep 25, 2004 at 05:40:27PM +0200, Egy?d Csaba wrote: I'm wonder if there is any possibility to modify a users password by updating the pg_shadow table. Why not use ALTER USER? http://www.postgresql.org/docs/7.4/static/sql-alteruser.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.766 / Virus Database: 513 - Release Date: 2004.09.17. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.766 / Virus Database: 513 - Release Date: 2004.09.17. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cvs update: waiting for anoncvs's lock
fixed On Fri, 24 Sep 2004, Michael Fuhr wrote: For the last couple of hours I've been trying to update my 8.0.0betaX sources with cvs update but I keep getting the following: cvs update: Updating . cvs update: Updating ChangeLogs cvs update: Updating MIGRATION cvs update: Updating config cvs update: [01:47:17] waiting for anoncvs's lock in /projects/cvsroot/pgsql-server/config cvs update: [01:47:47] waiting for anoncvs's lock in /projects/cvsroot/pgsql-server/config Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: 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
[GENERAL] psql password prompt
Hi All. There is one thing which somewhat annoys me, which is that psql always prompts me for a password. That makes it difficult for use in Makefile where I want to say 'make build_db', which would run psql for each stored procedure which needs to be updated. Is there a way to specify that password somehow in a command line like isql ( sybase cli client ) does? -- Dmitri Priimak ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] psql password prompt
In epistula a Dmitri Priimak, die horaque Sat, Sep 25, 2004 at 09:48:54AM -0700: Hi All. There is one thing which somewhat annoys me, which is that psql always prompts me for a password. That makes it difficult for use in Makefile where I want to say 'make build_db', which would run psql for each stored procedure which needs to be updated. In that case, I would dump all operations into an SQL script and run psql once from that script, if possible. Other options (ranging from totally insecure to more secure than password authentication and from easy to complex in implementation) are available, if you have administrative control over the PostgreSQL-server: - completely disable authentication (in $PG_DATA/pg_hba.conf) - disable authentication for your machine only - run the update commands on the same machine as PostgreSQL runs and set the Unix domain socket to ident sameuser authentication - use kerberos authentication Otherwise I do not know of any other possibility to resolve your problem other than hacking psql and implementing the desired parameter. Is there a way to specify that password somehow in a command line like isql ( sybase cli client ) does? That would be truly poor idea. The command line of any process is normally visible to every user on the system via the `ps' command: == password.sh == #! /bin/sh sleep 10 == password.sh == % sh password.sh sleep 1; ps | grep password 3233 p2 SN+0:00,01 sh password.sh --password myPassword Cheers, Peter -- Wir leben in einer Welt, worin ein Narr viele Narren, aber ein weiser Mann nur wenige Weise macht. -- Immanuel Kant ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] psql password prompt
On Sep 25, 2004, at 12:48 PM, Dmitri Priimak wrote: There is one thing which somewhat annoys me, which is that psql always prompts me for a password. That makes it difficult for use in Makefile where I want to say 'make build_db', which would run psql for each stored procedure which needs to be updated. Is there a way to specify that password somehow in a command line like isql ( sybase cli client ) does? You should be able to leave out the password altogether if you setup a .pgpass file in your home directory. http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html Best, John DeSoi, Ph.D. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] SMgrRelation hashtable corrupted
Hello, Im building a small web app (written in JSP) and using PostgreSQL 8.0 Beta 2 as the backend running on Windows XP (installed with pgInstaller). Ocassionally, I get this SQLException message in my web page: SMgrRelation hashtable corrupted If I refresh the web page it error goes away. I searched the lists and it looked like someone got this with beta 1. Just wondering if anyone found a solution. Thanks!
Re: [GENERAL] Replication options?
logging of the commit is done after the WAL operation, it must be assured that WAL replay during crash recovery also causes replication log journal to be recovered or repeated. In short, the replication log must be covered by the same redo mechanism the crash recovery system uses. This I will have to verify with our programmers as to exactly when the replication occurs. Joshua, you never followed up to this one. As of 1.3.1 (the current version) we also perform wal processing to insure that the transaction is correctly replicated in case of a crash. Sincerely, Joshua D. Drake Jan -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] serial data type
Can I use the serial data type in lieu of an 'auto_number' field? I asked something like this some months ago and it seems that auto_number fields were addressed through a combination of triggers and procedures to ensure that there were do duplicate KEYS generated. Is it realistic to use the serial data type as a KEY? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SMgrRelation hashtable corrupted
TroyGeek [EMAIL PROTECTED] writes: I'm building a small web app (written in JSP) and using PostgreSQL 8.0 Beta 2 as the backend running on Windows XP (installed with pgInstaller). Ocassionally, I get this SQLException message in my web page: SMgrRelation hashtable corrupted I suspect this is a dangling-reference problem, but I don't see where it could be coming from. Can you provide a test case? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] serial data type
Tom Allison [EMAIL PROTECTED] writes: Can I use the serial data type in lieu of an 'auto_number' field? What are the exact semantics of an auto_number field? I asked something like this some months ago and it seems that auto_number fields were addressed through a combination of triggers and procedures to ensure that there were do duplicate KEYS generated. Is it realistic to use the serial data type as a KEY? Lots and lots of people do. If you're just looking for a unique key column for a single table, it works fine. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] serial data type
Doug McNaught wrote: Is it realistic to use the serial data type as a KEY? Lots and lots of people do. If you're just looking for a unique key column for a single table, it works fine. -Doug This is essentially what I'm looking for. Any idea how to set up a timestamp=now on every insert/update ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] IN or JOIN
please CC me as I am on digest --- I have three tables, simplified for, well, simplicity :-) CREATE TABLE Usrs ( usr_id serial primary NOT NULL, name text NOT NULL, login text NOT NULL, CONSTRAINT PK_Usrs PRIMARY KEY (usr_id) ); CREATE TABLE EmailAddrs ( email_addr_id SERIAL NOT NULL, email_addr VARCHAR(255) NOT NULL UNIQUE, CONSTRAINT PK_EmailAddrs PRIMARY KEY (email_addr_id) ); CREATE TABLE UsrEmails ( usr_id INT4 NOT NULL, email_addr_id INT4 NOT NULL, CONSTRAINT PK_UsrEmails PRIMARY KEY (usr_id, email_addr_id) ); ALTER TABLE UsrEmails ADD CONSTRAINT EmailAddrs11_0MUsrEmail FOREIGN KEY (email_addr_id) REFERENCES EmailAddrs (email_addr_id); ALTER TABLE UsrEmails ADD CONSTRAINT UsrEmailTypes11_0MUsrEmails FOREIGN KEY (usr_email_type_id) REFERENCES UsrEmailTypes (usr_email_type_id); multiple 'Usrs' can have the same name, but different logins. I want to find the count of usrs that: have the name 'some_name' and have the email 'some_email' - Should I use a JOIN or an IN? If the 'IN' example below is right, and there is either: NO Usr with name='some_name' OR NO email with email='some_email' will it return a NULL, or a '0' count? my thought for an IN: -- SELECT COUNT(*) FROM UsrEmails WHERE usr_id IN (SELECT usr_id FROM Usrs WHERE name='some_name'::text) AND email_addr_id=(SELECT email_addr_id FROM Emails WHERE email='some_email'::text); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] serial data type
On Sat, Sep 25, 2004 at 07:20:23PM -0400, Tom Allison wrote: Any idea how to set up a timestamp=now on every insert/update ? when you create your table, use: create table mytable ( id serial primary key, updated timestamp default(now()), mydata int4 ); Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] serial data type
Joseph Healy [EMAIL PROTECTED] writes: On Sat, Sep 25, 2004 at 07:20:23PM -0400, Tom Allison wrote: Any idea how to set up a timestamp=now on every insert/update ? when you create your table, use: create table mytable ( id serial primary key, updated timestamp default(now()), mydata int4 ); That won't change the timestamp on UPDATE queries; you need a trigger for that. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] using COPY table FROM STDIN within script run as psql
Would you provide a reproducable example? Also, what PostgreSQL version are you using? --- Kevin Murphy wrote: This is a tip for the record in case it helps somebody else in the future. I have an import script that relies on a stored procedure that runs as a trigger on inserts into a temporary table. The script looks like this: -- create table -- ... -- define procedure and trigger -- ... -- import data via COPY command: COPY temp_table FROM STDIN WITH NULL AS ''; However, when run as psql -f import.sql data.file, it does not work if you use the SQL COPY command, even if you are running psql on the database server. You get an error like this: ERROR: missing data for column somecol. An interesting red-herring is that the column mentioned is not necessarily the first column in the table! The solution is to use the psql \COPY command instead (and remove the trailing semi-colon, which cannot be used with psql commands). I.e. this command will work: \COPY temp_table FROM STDIN WITH NULL AS ''; -Kevin Murphy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
