Re: [GENERAL] conditional rule not applied
On Thu, 7 Jan 2010 21:04:45 -0700, Scott Marlowe wrote: > On Wed, Dec 30, 2009 at 6:39 PM, Seb wrote: >> CREATE RULE footwear_nothing_upd AS ON UPDATE TO footwear DO >> INSTEAD NOTHING; CREATE RULE footwear_newshoelaces_upd AS ON >> UPDATE TO footwear WHERE NEW.sl_name <> OLD.sl_name AND >> OLD.sl_name IS NULL DO INSERT INTO shoelaces (sh_id, sl_name) >> VALUES(NEW.sh_id, NEW.sl_name); > Isn't that first rule gonna always fire and make the second one a > NOOP? No, the second is an implied ALSO, so it gets added to the DO INSTEAD NOTHING. This is actually the approach recommended in the man page for CREATE RULE where the reasons for doing that are described. The problem with this is that it always displays the message "UPDATE 0" when in fact the second rule may have also been applied with the INSERT. I posted this question to the postgresql.sql NG, where some discussion ensued. -- Seb -- 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] WAL Log Shipping - Warm Standby not working under 8.3.7
On Jan 8, 2010, at 4:50 PM, Erik Jones wrote:
>
> On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
>
>> OK,
>>
>> So what am I doing wrong here?
>>
>> Installed PG 8.3.7 on Slave machine
>>
>> Restored from last evening's backup from the master DB to make the rsync
>> across the network finish sooner.
>>
>> Shut down the PG instance on the slave machine
>>
>> Ran a script that does the following:
>>
>> select pg_start_backup('Master_Backup');
>> rsync -rvlpogtz ${masterdb}/* ${slave_dbus...@${slave_host}:${slavedb}
>> select pg_stop_backup();
>> ssh ${slave_dbus...@${slave_host} rm ${slavedb}/postmaster.pid 2>/dev/null
>> ssh ${slave_dbus...@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null
>> ssh ${slave_dbus...@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l
>> logfile start
>
> Is ${slave_backup_path} your archive directory? Why are you deleting all of
> you archives there?
Also, what are the contents of your recovery.conf file? Are you using
pg_standby? The typical setup is to clear /pg_xlog on your standby
and use pg_standby to recovery files directly from your archive directory?
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
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] WAL Log Shipping - Warm Standby not working under 8.3.7
To clean up from a prior run.
Erik Jones wrote:
On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
> OK,
>
> So what am I doing wrong here?
>
> Installed PG 8.3.7 on Slave machine
>
> Restored from last evening's backup from the master DB to make the rsync
> across the network finish sooner.
>
> Shut down the PG instance on the slave machine
>
> Ran a script that does the following:
>
> select pg_start_backup('Master_Backup');
> rsync -rvlpogtz ${masterdb}/* ${slave_dbus...@${slave_host}:${slavedb}
> select pg_stop_backup();
> ssh ${slave_dbus...@${slave_host} rm ${slavedb}/postmaster.pid 2>/dev/null
> ssh ${slave_dbus...@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null
> ssh ${slave_dbus...@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l logfile
> start
Is ${slave_backup_path} your archive directory? Why are you deleting all of
you archives there?
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
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] WAL Log Shipping - Warm Standby not working under 8.3.7
On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
> OK,
>
> So what am I doing wrong here?
>
> Installed PG 8.3.7 on Slave machine
>
> Restored from last evening's backup from the master DB to make the rsync
> across the network finish sooner.
>
> Shut down the PG instance on the slave machine
>
> Ran a script that does the following:
>
> select pg_start_backup('Master_Backup');
> rsync -rvlpogtz ${masterdb}/* ${slave_dbus...@${slave_host}:${slavedb}
> select pg_stop_backup();
> ssh ${slave_dbus...@${slave_host} rm ${slavedb}/postmaster.pid 2>/dev/null
> ssh ${slave_dbus...@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null
> ssh ${slave_dbus...@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l logfile
> start
Is ${slave_backup_path} your archive directory? Why are you deleting all of
you archives there?
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a kinder, gentler log_exector_stats?
Well, kinder and gentler on my disks, at least. I'm hoping for something more terse than what I'm seeing in my default 8.4.2 install, and also something that can be combined with the functionality of log_min_duration_statement. Is there such a thing? My goal is to achieve some accounting on which clients are using what percentage of our server capacity. It doesn't have to be perfect, but it should be fairly accurate. -- 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] Rows missing from table despite FK constraint
Alban Hertroys writes: > You seem to know what you're doing, but just in case we missed something as > this is strange enough to have even the devs scratching their heads. The rows > are there, so it _has_ to be an index or a transaction visibility issue... The successful fetch-by-ctid test seems to have eliminated the transaction-visibility-problem theory too. The whole thing is passing strange at this point. 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
Re: [GENERAL] Rows missing from table despite FK constraint
On 8 Jan 2010, at 18:28, Tom Lane wrote: >> # select attachment_id from attachment where ctid = '(603713,1)'; >> attachment_id >> --- >> 15460683 >> (1 row) > >> # select attachment_id from attachment where attachment_id = 15460683; >> attachment_id >> --- >> (0 rows) > > Oh, so the row *is* there. What the above says is that you have a > corrupt index on attachment_id, which you should be able to fix via > REINDEX. However, I'm still a bit confused, because corrupt indexes > don't normally cause a problem for pg_dump (which is just doing SELECT * > or COPY, so the index wouldn't be consulted). Are the dumps you are > talking about perhaps made with something other than pg_dump? Would pg_dump still not consult the index if someone sets enable_seqscan=false in the config file? To Konrad: Did you turn off seqscans in the postgres.conf? Could you try a "REINDEX TABLE attachment" again in case you somehow reindexed the wrong index or table? You seem to know what you're doing, but just in case we missed something as this is strange enough to have even the devs scratching their heads. The rows are there, so it _has_ to be an index or a transaction visibility issue... Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b47b35810731946694119! -- 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] Huge iowait during checkpoint finish
On Fri, Jan 8, 2010 at 3:07 PM, Greg Smith wrote: > Basically, you have a couple of standard issues here: > > 1) You're using RAID-5, which is not known for good write performance. Are > you sure the disk array performs well on writes? And if you didn't > benchmark it, you can't be sure. This can be doubly bad if he's now moved to a set of disks that are properly obeying fsync but was on disks that were lying about it before. -- 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] Huge iowait during checkpoint finish
Anton Belyaev wrote: I think all the IOwait comes during sync time, which is 80 s, according to the log entry. I believe you are correctly diagnosing the issue. The "sync time" entry in the log was added there specifically to make it easier to confirm this problem you're having exists on a given system. bgwriter_lru_maxpages = 0 # BG writer is off checkpoint_segments = 45 checkpoint_timeout = 60min checkpoint_completion_target = 0.9 These are reasonable settings. You can look at pg_stat_bgwriter to get more statistics about your checkpoints; grab a snapshot of that now, another one later, and then compute the difference between the two. I've got an example of that http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm You should be aiming to have a checkpoint no more than every 5 minutes, and on a write-heavy system shooting for closer to every 10 is probably more appropriate. Do you know how often they're happening on yours? Two pg_stat_bgwriter snapshots from a couple of hours apart, with a timestamp on each, can be used to figure that out. I had mostly the same config with my 8.3 deployment. But hardware is different: Disk is software RAID-5 with 3 hard drives. Operating system is Ubuntu 9.10 Server x64. Does the new server have a lot more RAM than the 8.3 one? Some of the problems in this area get worse the more RAM you've got. Does the new server use ext4 while the old one used ext3? Basically, you have a couple of standard issues here: 1) You're using RAID-5, which is not known for good write performance. Are you sure the disk array performs well on writes? And if you didn't benchmark it, you can't be sure. 2) Linux is buffering a lot of writes that are only making it to disk at checkpoint time. This could be simply because of (1)--maybe the disk is always overloaded. But it's possible this is just due to excessive Linux buffering being lazy about the writes. I wrote something about that topic at http://notemagnet.blogspot.com/2008/08/linux-write-cache-mystery.html you might find interesting. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] pgadmin save password
Le 08/01/2010 21:22, glaucomag a écrit : > Hi, I've a problem with pgadmin. If I access to database with user X > and I save password, when I access to database from shell (psql) > password is not required. Of course pg_hba.conf is: > > local database X md5 > > If I don't save password in pgadmin, it's ok (psql required password). > The question is this: is it possible that pgadmin save password also > for psql? And where pgadmin save password? > pgadmin creates the .pgpass file that all libpq programs use to easily connect a user to a database. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgadmin save password
Hi, I've a problem with pgadmin. If I access to database with user X and I save password, when I access to database from shell (psql) password is not required. Of course pg_hba.conf is: local database X md5 If I don't save password in pgadmin, it's ok (psql required password). The question is this: is it possible that pgadmin save password also for psql? And where pgadmin save password? Thank you -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pre-calculate hash join
When joining two large tables [common in warehousing], a hash join is commonly selected. Calculating hash values for the merge phase is CPU intensive. Is there any way to pre-calculate value hashes to save that time? Would it even grant any performance to skip the build phase of the hash join? -Warren -- 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] Index question on postgres
From: akp geek [mailto:akpg...@gmail.com]
Sent: Thursday, January 07, 2010 9:04 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
{snip}
Why would the index I have created not being used?
>>
The index you have created will not be used in several circumstances.
For instance:
1. It is faster to do a table scan than to use the index, despite
up to date statistics
2. The distribution of the data has changed since the last time
you analyzed the database
Consider a truly horrible case, an index on a single character. As it
turns out, this field contains exactly two values: 'M' or 'F' for male
or female. About 50% of the data is 'M' and about 50% is 'F'. If we
were to use this index to scan the data, we will be loading the index
pages, and then popping all over the data pages following the index. It
will truly be an awful sight. We would spend far more effort than
simply doing a table scan. Fortunately, we have statistics which have
come to our rescue. They will tell the optimizer to simply ignore the
horribly defined index file and never use it in any circumstance.
Consider an even more horrible case, the same index, but we have not
updated statistics in months and we have automatic stats and vacuum
disabled. The only time statistics was run, there was a single 'F' in
the index and 44 'M' values. A query comes along looking for "sex =
'F'" and the optimizer decides to use the index. We can't blame the
poor optimizer. It's not his fault that statistical collection was
disabled. So he merrily informs the query planner to follow the index
to collect the data, and the query takes eons to complete.
In short, using the index is not always a good idea. It's a good idea
to use an index when it is faster than not using an index. If you were
to post the explain analyze output, experts here could tell you exactly
why the decisions were made to use an index or not to use an index. And
if an index should have been used, they can tell you what to do so that
the index will be used next time.
<<
Re: [GENERAL] Index question on postgres
OK.. got you. Regards On Fri, Jan 8, 2010 at 2:37 PM, Vick Khera wrote: > there ya go. the query plan will change based on the data statistics > on the tables and indexes. > > On Fri, Jan 8, 2010 at 2:09 PM, akp geek wrote: > > The volume of data is less in Test compared to prod. and I synced the > > postgresql.conf file in both environments >
Re: [GENERAL] Index question on postgres
there ya go. the query plan will change based on the data statistics on the tables and indexes. On Fri, Jan 8, 2010 at 2:09 PM, akp geek wrote: > The volume of data is less in Test compared to prod. and I synced the > postgresql.conf file in both environments -- 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] Index question on postgres
The volume of data is less in Test compared to prod. and I synced the postgresql.conf file in both environments Regards On Fri, Jan 8, 2010 at 1:55 PM, Vick Khera wrote: > On Thu, Jan 7, 2010 at 11:11 PM, akp geek wrote: > > I have query in production and test. The tables in both the environment > has > > the same structure ,indexes and constraints. But the in the test and the > > prod the explain plan is totally different. In test environment the query > is > > taking long time and noticed that indexes are not being utilized ? I am > not > > able to figure it Can you please share your thoughts? > > Is the same amount of data in both? Are the other configs of the > server the same? >
Re: [GENERAL] Index question on postgres
On Thu, Jan 7, 2010 at 11:11 PM, akp geek wrote: > I have query in production and test. The tables in both the environment has > the same structure ,indexes and constraints. But the in the test and the > prod the explain plan is totally different. In test environment the query is > taking long time and noticed that indexes are not being utilized ? I am not > able to figure it Can you please share your thoughts? Is the same amount of data in both? Are the other configs of the server the same? -- 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] Table appears on listing but can't drop it
On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver wrote: > On 01/08/2010 09:53 AM, Fernando Morgenstern wrote: > >> >> > > Actually what is strange is that your previous listing : > > postgres=# select '"' || datname || '"' from pg_database; > ?column? > - > "template1" > "template0" > "t1" > "skynet" > > is not the same as the one above: Oops should be "is not the same as the one below" > > > postgres=# select '"' || datname || '"' from pg_database; > ?column? > - > "template1" > "template0" > "postgres" > "t1" > "pgpool" > "skynet" > > In particular the presence of postgres,t1 and pgpool. > > Are you sure which cluster you are pointing at and whether the psql version > matches the server version? > > > -- > Adrian Klaver > adrian.kla...@gmail.com > -- Adrian Klaver adrian.kla...@gmail.com
[GENERAL] Huge iowait during checkpoint finish
Hello dear list members, I have strange problem with my new 8.4 deployment, which I never encountered on previous 8.3 deployment. IOwait values are extremely high exactly when Postgres finishes a checkpoint. During the checkpoint itself (which is quite lengthy) IOwait is very low. Why does this happen and how to fix it? Take a look at this chart: http://kpox.s3.amazonaws.com/cpu-day.png The lates (rightest) IOwait peak corresponds to this log entry: 2010-01-08 18:40:36 CET LOG: checkpoint complete: wrote 46357 buffers (35.4%); 0 transaction log file(s) added, 0 removed, 40 recycled; write=2502.800 s, sync=79.972 s, total=2583.184 s IOwait peak starts about 18:39:30 and finishes at 18:40:30. I think all the IOwait comes during sync time, which is 80 s, according to the log entry. Config details that might be related to the problem: bgwriter_lru_maxpages = 0 # BG writer is off checkpoint_segments = 45 checkpoint_timeout = 60min checkpoint_completion_target = 0.9 I had mostly the same config with my 8.3 deployment. But hardware is different: Disk is software RAID-5 with 3 hard drives. Operating system is Ubuntu 9.10 Server x64. Thanks. Anton. -- 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] Table appears on listing but can't drop it
On 01/08/2010 09:53 AM, Fernando Morgenstern wrote: Em 08/01/2010, às 15:49, Adrian Klaver escreveu: On 01/08/2010 08:55 AM, Fernando Morgenstern wrote: Hello, Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you suggested: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" Best Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.com Can you connect to it? -- Adrian Klaver adrian.kla...@gmail.com No, i get this: $ psql skynet psql: FATAL: database "skynet" does not exist I can create a database with the same name: postgres=# create database skynet; CREATE DATABASE postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "skynet" "skynet" And drop the newly created database: postgres=# drop database skynet; DROP DATABASE postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "pgpool" "skynet" Strange, isn't it? Actually what is strange is that your previous listing : postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" is not the same as the one above: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "pgpool" "skynet" In particular the presence of postgres,t1 and pgpool. Are you sure which cluster you are pointing at and whether the psql version matches the server version? -- Adrian Klaver adrian.kla...@gmail.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] Server name in psql prompt
Adrian Klaver wrote: In the case you describe the below might work: http://www.postgresql.org/docs/8.4/interactive/app-psql.html "Before starting up, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). " Thanks Adrian, link noted. Set up a system psqlrc. I have done this when working with multiple versions/multiple database clusters of Postgres on one machine to keep track. I've gone round a number of machines setting up a basic psqlrc file, and will mail the most obnox^H^H^H^H^H demanding users warning them of the issues. ii) Getting the prompt to display the actual hostname of the server, rather than what was put on the command line which might be an alias or dotted-quad address. Same link as above. %M The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location. I've just checked that and if I do psql -h postgres where postgres is a DNS alias to postgres1 then the expansion of %M is "postgres" not "postgres1". iii) Getting the prompt to display some other identifier from the server to identify the disc set Make either one of these different for each server. %:name: The value of the psql variable name. See the section Variables for details. %[ ... %] OK but if I understand you (and the docs) correctly I'd still need to find a way to set the variable on the client rather than having something fetched from the server. I thought earlier that I could use finger as a hack for querying the server, i.e. I could put e.g. a disc set name in /home/postgres/.plan. However I then realised that I'd need %M to be expanded before %`, so that I could do something like \set PROMPT1 '`finger postg...@%m|filter`: %/%R%# ' where filter only returned the bit that was needed. I've not tried this due to the ordering issue. Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur within the prompt. For example: testdb=> \set PROMPT1 '%[%033[1;33;40m%...@%/%R%[%033[0m%]%# ' results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals. Thanks, noted. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Table appears on listing but can't drop it
Em 08/01/2010, às 15:49, Adrian Klaver escreveu: > On 01/08/2010 08:55 AM, Fernando Morgenstern wrote: > >> Hello, >> >> Thanks for your quick answers. The extra space is indeed a copy-and-paste >> issue. Here it is the select that you suggested: >> >> postgres=# select '"' || datname || '"' from pg_database; >> ?column? >> - >> "template1" >> "template0" >> "t1" >> "skynet" >> >> >> Best Regards, >> --- >> >> Fernando Marcelo >> www.consultorpc.com >> ferna...@consultorpc.com > > Can you connect to it? > > -- > Adrian Klaver > adrian.kla...@gmail.com No, i get this: $ psql skynet psql: FATAL: database "skynet" does not exist I can create a database with the same name: postgres=# create database skynet; CREATE DATABASE postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "skynet" "skynet" And drop the newly created database: postgres=# drop database skynet; DROP DATABASE postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "pgpool" "skynet" Strange, isn't it? Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.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] WAL Log Shipping - Warm Standby not working under 8.3.7
OK,
So what am I doing wrong here?
Installed PG 8.3.7 on Slave machine
Restored from last evening's backup from the master DB to make the rsync across
the network finish sooner.
Shut down the PG instance on the slave machine
Ran a script that does the following:
select pg_start_backup('Master_Backup');
rsync -rvlpogtz ${masterdb}/* ${slave_dbus...@${slave_host}:${slavedb}
select pg_stop_backup();
ssh ${slave_dbus...@${slave_host} rm ${slavedb}/postmaster.pid 2>/dev/null
ssh ${slave_dbus...@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null
ssh ${slave_dbus...@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l logfile
start
When the slave PG database attempts to come up in recovery mode, it aborts
because it is looking for a log file that is extremely old and does not exist
on the master DB server. I believe the Master PG instance was restarted on
12/28/09 and has been running ever since. Is there a way to reset the "last
completed transaction" on a DB? Why is PG looking so far back for a WAL log to
begin recovery when so much has been done since the 28th including daily
backups?
<2010-01-07 10:54:23 MST>LOG: received immediate shutdown request
/mxl/var/pgsql/data/ha_copy.sh: line 103: 13976 Quitsleep 5
File /mxl/var/pgsql/data/stopslave found. Aborting Process.
<2010-01-07 10:54:28 MST>LOG: could not open file
"pg_xlog/000100F600E9" (log file 246, segment 233): N
o such file or directory
<2010-01-07 10:54:28 MST>LOG: redo done at F6/E8FFE378
<2010-01-07 10:54:28 MST>LOG: last completed transaction was at log time
2009-12-28 10:18:04.893307-07
Waiting for log: 000100F600E8
<2010-01-07 11:24:49 MST>FATAL: could not restore file
"000100F600E8" from archive: return code 15
Again, nothing was changed with the scripts or the replication process and this
worked just fine under 8.1.4.
Thanks!
On 1/8/10 8:10 AM, "Keaton Adams" wrote:
I did find some references to a fix of last-completed transaction time and I
looked in the postgresql-bugs archive, but I'm not having any luck confirming
that this is a problem in 8.3.7 and an upgrade to 8.3.9 would fix the issue.
postgresql 8.3.7 Fix incorrect logging of last-completed-transaction time
during PITR . Last transaction end time is now logged at end of recovery
and at each logged restart point (Simon) ...
On 1/7/10 12:53 PM, "Keaton Adams" wrote:
We had WAL Log shipping (warm standby) working fine under 8.1.4 but under 8.3.7
we can't get the slave to come up properly. Nothing has changed in our process
with regard to start_backup, rsync, stop_backup, bring up the warm standby
server in continuous recovery mode, but the failover DB won't start with the
following error:
<2010-01-07 10:54:23 MST>LOG: received immediate shutdown request
/mxl/var/pgsql/data/ha_copy.sh: line 103: 13976 Quitsleep 5
File /mxl/var/pgsql/data/stopslave found. Aborting Process.
<2010-01-07 10:54:28 MST>LOG: could not open file
"pg_xlog/000100F600E9" (log file 246, segment 233): No such file or
directory
<2010-01-07 10:54:28 MST>LOG: redo done at F6/E8FFE378
<2010-01-07 10:54:28 MST>LOG: last completed transaction was at log time
2009-12-28 10:18:04.893307-07
Waiting for log: 000100F600E8
<2010-01-07 11:24:49 MST>FATAL: could not restore file
"000100F600E8" from archive: return code 15
The log file in reference is very old and is not on the Master PG server in
pg_xlogs and the "last completed transaction" can't be right either. Is this a
bug or it is something we are doing wrong?
Thanks,
Keaton
psql (PostgreSQL) 8.3.7
contains support for command-line editing
RHEL 5 64 Bit
Linux ourservername 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64
x86_64 x86_64 GNU/Linux
Re: [GENERAL] Table appears on listing but can't drop it
On 01/08/2010 08:55 AM, Fernando Morgenstern wrote: Hello, Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you suggested: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" Best Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.com Can you connect to it? -- Adrian Klaver adrian.kla...@gmail.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] Rows missing from table despite FK constraint
Konrad Garus writes: > 2010/1/8 Tom Lane : >> What the above says is that you have a >> corrupt index on attachment_id, which you should be able to fix via >> REINDEX. > This is not correct. The dumps are made with pg_dump. We did reindex > on the table. I also tried looking for the row with another index and > with seq scan and could not see it. I'm still baffled then, and am starting to think that this really needs some investigation with a debugger. How are you with gdb? Or is there a chance of letting me or another developer poke at (a copy of) your database? 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
Re: [GENERAL] Rows missing from table despite FK constraint
2010/1/8 Adrian Klaver : > This looks a lot like this thread: > http://archives.postgresql.org/pgsql-general/2009-12/msg00726.php > > Could we see the schema and indexes for this table? Table "public.attachment" Column |Type | Modifiers | Description +-+---+- attachment_id | integer | not null | entity_kind| character varying(15) | | entity_id | integer | | attached_by| integer | not null | when_attached | timestamp without time zone | not null | when_uploaded | timestamp without time zone | | file_name | character varying(255) | not null | file_size | integer | not null | hash | character varying(50) | | description| character varying(300) | | thumb | bytea | | target_entity_kind | character varying(15) | | target_entity_id | integer | | file_size_enc | bigint | | hash_enc | character varying(50) | | secure_key | bytea | | status | character varying(50) | | width | integer | | height | integer | | lat| numeric(10,7) | | lon| numeric(10,7) | | created_date | timestamp without time zone | | created_time | integer | | Indexes: "attachment_pkey" PRIMARY KEY, btree (attachment_id) "attachment_by_entity" btree (entity_kind, entity_id) "attachment_by_entity_id" btree (entity_id) "attachment_by_target_entity" btree (target_entity_kind, target_entity_id) "attachment_by_uploaded" btree (when_uploaded) "attachment_by_user" btree (attached_by) "attachment_hash_ix" btree (hash) Foreign-key constraints: "fk8af75923d38260d2" FOREIGN KEY (attached_by) REFERENCES usr(usr_id) Rules: attachment_no_delete AS ON DELETE TO attachment DO INSTEAD SELECT no_delete() AS no_delete Has OIDs: no -- Konrad Garus -- 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] Rows missing from table despite FK constraint
On 01/08/2010 09:31 AM, Konrad Garus wrote: 2010/1/8 Tom Lane: Oh, so the row *is* there. Right. I'm happy to see it. What the above says is that you have a corrupt index on attachment_id, which you should be able to fix via REINDEX. This is not correct. The dumps are made with pg_dump. We did reindex on the table. I also tried looking for the row with another index and with seq scan and could not see it. This looks a lot like this thread: http://archives.postgresql.org/pgsql-general/2009-12/msg00726.php Could we see the schema and indexes for this table? -- Adrian Klaver adrian.kla...@gmail.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] Rows missing from table despite FK constraint
2010/1/8 Tom Lane : > Oh, so the row *is* there. Right. I'm happy to see it. > What the above says is that you have a > corrupt index on attachment_id, which you should be able to fix via > REINDEX. This is not correct. The dumps are made with pg_dump. We did reindex on the table. I also tried looking for the row with another index and with seq scan and could not see it. -- Konrad Garus -- 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] Rows missing from table despite FK constraint
Konrad Garus writes: > 2010/1/8 Tom Lane : >> Just to confirm, if you try to select any of these rows by ctid, ie >> select * from tablename where ctid = '(603713,1)'; >> you get nothing? What *should* happen is that you get the row if you >> mention offset 1, 3, or 5, but nothing if you say 2 or 4. > How about this? > # select attachment_id from attachment where ctid = '(603713,1)'; > attachment_id > --- > 15460683 > (1 row) > # select attachment_id from attachment where attachment_id = 15460683; > attachment_id > --- > (0 rows) Oh, so the row *is* there. What the above says is that you have a corrupt index on attachment_id, which you should be able to fix via REINDEX. However, I'm still a bit confused, because corrupt indexes don't normally cause a problem for pg_dump (which is just doing SELECT * or COPY, so the index wouldn't be consulted). Are the dumps you are talking about perhaps made with something other than pg_dump? 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
Re: [GENERAL] Rows missing from table despite FK constraint
2010/1/8 Tom Lane : > Just to confirm, if you try to select any of these rows by ctid, ie > select * from tablename where ctid = '(603713,1)'; > you get nothing? What *should* happen is that you get the row if you > mention offset 1, 3, or 5, but nothing if you say 2 or 4. How about this? # select attachment_id from attachment where ctid = '(603713,1)'; attachment_id --- 15460683 (1 row) # select attachment_id from attachment where attachment_id = 15460683; attachment_id --- (0 rows) -- Konrad Garus -- 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] Rows missing from table despite FK constraint
Konrad Garus writes: > 2010/1/8 Alvaro Herrera : >> I'm a bit surprised by the block numbers in the block header vs. t_self ... >> I would have guessed that they come from a different segment (and >> the numbers seem to match, as 603713 % 131072 = 79425), but Konrad >> doesn't seem to be using the foo.4 file. > I am not sure I understand what you say, but the 922494 file has 12 > parts (922494, 922494.1 through 922494.11). The file I gave you is, > indeed, dumped from 922494.4. Yeah, it sounds like you did it right, and anyway the block ids prove this is the right block --- the forward links in HOT-updated tuples have to point to the same block. I'm just completely baffled at this point. The data appears perfectly okay according to pg_filedump, and if pg_filedump can read the page then the backend should be able to as well. Just to confirm, if you try to select any of these rows by ctid, ie select * from tablename where ctid = '(603713,1)'; you get nothing? What *should* happen is that you get the row if you mention offset 1, 3, or 5, but nothing if you say 2 or 4. 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
Re: [GENERAL] Server name in psql prompt
On 01/08/2010 08:59 AM, Mark Morgan Lloyd wrote: hubert depesz lubaczewski wrote: On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote: Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? what exactly is the problem with distributing your own .psqlrc? for me it's one of the first things that I do - I setup environment. Thanks everybody for the comments. The problem is that in an environment where the end-users generally have enough nous (or are sufficiently assertive) to run their own systems (e.g. an engineering department) there is still a requirement to protect shared resources like a database. It's not really feasible for the overall sysadmin to work his way around all possible machines, work out which distro each is running, and install a suitable psqlrc in the place expected by that distro's psql. It's even less feasible to install a shim that forces default command-line parameters. When I referred to a disc set I wasn't thinking about something in the context of PostgreSQL, I was thinking about a group of discs in removable (Compaq) caddies that might be transferred to one of a number of chassis. At present I've got one chassis here into which I'm putting one of two disc sets, both of which are the 8.4 upgrade target: I'd like to be able to confirm from the client which set is in the chassis. After playing some more I think there are actually three issues: i) Getting psql to take its initial defaults, i.e. if there isn't a psqlrc file, from the server (e.g. for the prompt). In the case you describe the below might work: http://www.postgresql.org/docs/8.4/interactive/app-psql.html "Before starting up, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). " Set up a system psqlrc. I have done this when working with multiple versions/multiple database clusters of Postgres on one machine to keep track. ii) Getting the prompt to display the actual hostname of the server, rather than what was put on the command line which might be an alias or dotted-quad address. Same link as above. %M The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location. iii) Getting the prompt to display some other identifier from the server to identify the disc set Make either one of these different for each server. %:name: The value of the psql variable name. See the section Variables for details. %[ ... %] Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur within the prompt. For example: testdb=> \set PROMPT1 '%[%033[1;33;40m%...@%/%R%[%033[0m%]%# ' results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals. I don't think anybody else thinks this is an issue so I guess all I can say is thanks for listening :-) -- Adrian Klaver adrian.kla...@gmail.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] Rows missing from table despite FK constraint
2010/1/8 Alvaro Herrera : > I'm a bit surprised by the block numbers in the block header vs. t_self ... > I would have guessed that they come from a different segment (and > the numbers seem to match, as 603713 % 131072 = 79425), but Konrad > doesn't seem to be using the foo.4 file. I am not sure I understand what you say, but the 922494 file has 12 parts (922494, 922494.1 through 922494.11). The file I gave you is, indeed, dumped from 922494.4. -- Konrad Garus -- 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] Server name in psql prompt
hubert depesz lubaczewski wrote: On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote: Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? what exactly is the problem with distributing your own .psqlrc? for me it's one of the first things that I do - I setup environment. Thanks everybody for the comments. The problem is that in an environment where the end-users generally have enough nous (or are sufficiently assertive) to run their own systems (e.g. an engineering department) there is still a requirement to protect shared resources like a database. It's not really feasible for the overall sysadmin to work his way around all possible machines, work out which distro each is running, and install a suitable psqlrc in the place expected by that distro's psql. It's even less feasible to install a shim that forces default command-line parameters. When I referred to a disc set I wasn't thinking about something in the context of PostgreSQL, I was thinking about a group of discs in removable (Compaq) caddies that might be transferred to one of a number of chassis. At present I've got one chassis here into which I'm putting one of two disc sets, both of which are the 8.4 upgrade target: I'd like to be able to confirm from the client which set is in the chassis. After playing some more I think there are actually three issues: i) Getting psql to take its initial defaults, i.e. if there isn't a psqlrc file, from the server (e.g. for the prompt). ii) Getting the prompt to display the actual hostname of the server, rather than what was put on the command line which might be an alias or dotted-quad address. iii) Getting the prompt to display some other identifier from the server to identify the disc set. I don't think anybody else thinks this is an issue so I guess all I can say is thanks for listening :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Table appears on listing but can't drop it
Em 08/01/2010, às 14:48, Tom Lane escreveu: > Adrian Klaver writes: >> On 01/08/2010 08:39 AM, Fernando Morgenstern wrote: >>> Name| Owner | Encoding | Collation |Ctype| Access >>> privileges >>> ---+--+--+-+-+--- >>> skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | >>> t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | >>> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres >>> : postgres=CTc/postgres >>> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres >>> : postgres=CTc/postgres > >> You have a space at the beginning of the name. Try: >> drop database " skynet"; > > I'm not sure about that, because the whole row seems to be offset in > his email. That could be just copy-and-paste sloppiness. Still, > some sort of non-printing character in the name seems to be indicated, > else he'd not have been able to create another db with name "skynet". > > Try something like > select '"' || datname || '"' from pg_database > to get a clearer view of what's really in there. > > regards, tom lane Hello, Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you suggested: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" Best Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.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] Rows missing from table despite FK constraint
Konrad Garus escribió: > 2010/1/8 Tom Lane : > > So, no wraparound problem ... odder and odder. Could we see the whole > > -i -f printout for that block? You trimmed some of it before, > > particularly the block header. > > Attached. > > Since data on disk looks correct, is it possible to diagnose it on a > higher level? Could the damage be done by vacuum? I'm a bit surprised by the block numbers in the block header vs. t_self ... I would have guessed that they come from a different segment (and the numbers seem to match, as 603713 % 131072 = 79425), but Konrad doesn't seem to be using the foo.4 file. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Table appears on listing but can't drop it
Adrian Klaver writes: > On 01/08/2010 08:39 AM, Fernando Morgenstern wrote: >> Name| Owner | Encoding | Collation |Ctype| Access >> privileges >> ---+--+--+-+-+--- >> skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | >> t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | >> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres >> : postgres=CTc/postgres >> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres >> : postgres=CTc/postgres > You have a space at the beginning of the name. Try: > drop database " skynet"; I'm not sure about that, because the whole row seems to be offset in his email. That could be just copy-and-paste sloppiness. Still, some sort of non-printing character in the name seems to be indicated, else he'd not have been able to create another db with name "skynet". Try something like select '"' || datname || '"' from pg_database to get a clearer view of what's really in there. 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
Re: [GENERAL] Rows missing from table despite FK constraint
2010/1/8 Tom Lane : > So, no wraparound problem ... odder and odder. Could we see the whole > -i -f printout for that block? You trimmed some of it before, > particularly the block header. Attached. Since data on disk looks correct, is it possible to diagnose it on a higher level? Could the damage be done by vacuum? -- Konrad Garus missing_block Description: Binary data -- 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] Table appears on listing but can't drop it
On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote: > postgres=# \l > List of databases >Name| Owner | Encoding | Collation |Ctype| Access > privileges > ---+--+--+-+-+--- > skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres > : > postgres=CTc/postgres > template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres > : > postgres=CTc/postgres There's an extra space at the beginning of the "skynet" line, could it be that you created it with special characters in the name? To check, I'd try: select quote_ident(datname) from pg_database; -- Sam http://samason.me.uk/ -- 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] Table appears on listing but can't drop it
On 01/08/2010 08:39 AM, Fernando Morgenstern wrote: Hello, I'm running version 8.4.1 and have a table that appears on listing ( when i run \l ) but i can't drop it. Example: postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+--+--+-+-+--- skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres postgres=# drop database skynet; ERROR: database "skynet" does not exist I intentionally removed other databases name. Also, i verified that i can run CREATE DATABASE skynet having two databases with the same name. Any ideas of what causes this problem? Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.com You have a space at the beginning of the name. Try: drop database " skynet"; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table appears on listing but can't drop it
Hello, I'm running version 8.4.1 and have a table that appears on listing ( when i run \l ) but i can't drop it. Example: postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+--+--+-+-+--- skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres postgres=# drop database skynet; ERROR: database "skynet" does not exist I intentionally removed other databases name. Also, i verified that i can run CREATE DATABASE skynet having two databases with the same name. Any ideas of what causes this problem? Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.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] Rows missing from table despite FK constraint
Konrad Garus writes: > Latest checkpoint's NextXID: 0/83037806 So, no wraparound problem ... odder and odder. Could we see the whole -i -f printout for that block? You trimmed some of it before, particularly the block header. 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
Re: [GENERAL] Rows missing from table despite FK constraint
Just a reminder - these rows are over 6 months old and were lost at night when the system was lightly used. -- Konrad Garus -- 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] Rows missing from table despite FK constraint
2010/1/8 Tom Lane : > Also, what are the XMINs of the non-missing tuples in the adjacent > blocks? # /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main/ pg_control version number:833 Catalog version number: 200711281 Database system identifier: 5246886698902745063 Database cluster state: in production pg_control last modified: Fri 08 Jan 2010 10:20:56 AM CST Latest checkpoint location: 1D6/186B6BA0 Prior checkpoint location:1D6/165DAF60 Latest checkpoint's REDO location:1D6/174C8FB8 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/83037806 Latest checkpoint's NextOID: 142180690 Latest checkpoint's NextMultiXactId: 2250472 Latest checkpoint's NextMultiOffset: 5954794 Time of latest checkpoint:Fri 08 Jan 2010 10:18:33 AM CST Minimum recovery ending location: 0/0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Maximum length of locale name:128 LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 2 mins around the missing rows: ctid | xmin | attachment_id -+--+--- (603712,67) | 17140362 | 15460680 (603712,69) | 17140363 | 15460871 (603712,71) | 17140364 | 15460681 (603712,73) | 17140368 | 15460872 (603712,75) | 17140369 | 15460682 (603712,78) | 17140373 | 15460873 (603714,1) | 17140379 | 15460685 (603714,3) | 17140380 | 15460473 (603714,5) | 17140381 | 15460875 (603714,7) | 17140382 | 15460686 (603714,9) | 17140383 | 15460474 -- Konrad Garus -- 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] Rows missing from table despite FK constraint
I wrote: > Huh. Nothing obviously wrong with the data ... maybe an xid wraparound > issue? What's your current XID counter? (pg_controldata is the easiest > way to answer that) Also, what are the XMINs of the non-missing tuples in the adjacent blocks? 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
Re: [GENERAL] Rows missing from table despite FK constraint
Konrad Garus writes: > 2010/1/8 Tom Lane : >> So which of these rows are invisible? According to the flags >> items 1, 3 and 5 should be visible while 2 and 4 are dead versions >> (of 3 and 5 respectively). > All 3 are invisible, and at the same time they are the only 3 rows > missing from the table. Huh. Nothing obviously wrong with the data ... maybe an xid wraparound issue? What's your current XID counter? (pg_controldata is the easiest way to answer that) 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
Re: [GENERAL] Rows missing from table despite FK constraint
2010/1/8 Tom Lane : > So which of these rows are invisible? According to the flags > items 1, 3 and 5 should be visible while 2 and 4 are dead versions > (of 3 and 5 respectively). All 3 are invisible, and at the same time they are the only 3 rows missing from the table. -- Konrad Garus -- 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] Rows missing from table despite FK constraint
Konrad Garus writes: > OK, I got it. Attached is the dump of the missing block. So which of these rows are invisible? According to the flags items 1, 3 and 5 should be visible while 2 and 4 are dead versions (of 3 and 5 respectively). 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
Re: [GENERAL] Rows missing from table despite FK constraint
OK, I got it. Attached is the dump of the missing block. -- Konrad Garus missing_block Description: Binary data -- 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] Rows missing from table despite FK constraint
2010/1/8 Konrad Garus : > I'm unsure about it, because the resulting file does not seem to have > the rows I saw listed for block 603712 or 603714. I checked by text in > VARCHAR columns. I must've done something wrong. I found that row listed at: Block 603712 (header etc.) Item 15 -- Length: 184 Offset: 5496 (0x1578) Flags: NORMAL XMIN: 8124 XMAX: 0 CID|XVAC: 0 Block Id: 79424 linp Index: 15 Attributes: 19 Size: 32 infomask: 0x0903 (HASNULL|HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) t_bits: [0]: 0xff [1]: 0x79 [2]: 0x07 Has ctid equal (79424,15). How do I run pg_filedump for rows with ctid between (603712,78) and (603714,1)? -- Konrad Garus -- 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] Rows missing from table despite FK constraint
How shall I do it? Is this correct: 1. Run: select ctid, * from attachment where ... on the table with such a WHERE clause that includes rows around the missing ones. ctid around missing rows seems to be (603712,78) and (603714,1). Note that 603713 is missing. 2. Run: select relfilenode from pg_class where relname = 'attachment';" (returns 922494) 3. Run: pg_filedump -i -f -R 603712 603714 /var/lib/postgresql/8.3/main/base/922438/922494 > myfile I'm unsure about it, because the resulting file does not seem to have the rows I saw listed for block 603712 or 603714. I checked by text in VARCHAR columns. -- Konrad Garus -- 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] dynamic insert in plpgsql
2010/1/8 Grzegorz Jaśkiewicz : > what is that "(t" in the SELECT there for ? > or is it just typo, or something missing/etc ? > ignore it. That's cast, for type t (table). -- 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] WAL Log Shipping - Warm Standby not working under 8.3.7
I did find some references to a fix of last-completed transaction time and I looked in the postgresql-bugs archive, but I'm not having any luck confirming that this is a problem in 8.3.7 and an upgrade to 8.3.9 would fix the issue. postgresql 8.3.7 Fix incorrect logging of last-completed-transaction time during PITR . Last transaction end time is now logged at end of recovery and at each logged restart point (Simon) ... On 1/7/10 12:53 PM, "Keaton Adams" wrote: We had WAL Log shipping (warm standby) working fine under 8.1.4 but under 8.3.7 we can't get the slave to come up properly. Nothing has changed in our process with regard to start_backup, rsync, stop_backup, bring up the warm standby server in continuous recovery mode, but the failover DB won't start with the following error: <2010-01-07 10:54:23 MST>LOG: received immediate shutdown request /mxl/var/pgsql/data/ha_copy.sh: line 103: 13976 Quitsleep 5 File /mxl/var/pgsql/data/stopslave found. Aborting Process. <2010-01-07 10:54:28 MST>LOG: could not open file "pg_xlog/000100F600E9" (log file 246, segment 233): No such file or directory <2010-01-07 10:54:28 MST>LOG: redo done at F6/E8FFE378 <2010-01-07 10:54:28 MST>LOG: last completed transaction was at log time 2009-12-28 10:18:04.893307-07 Waiting for log: 000100F600E8 <2010-01-07 11:24:49 MST>FATAL: could not restore file "000100F600E8" from archive: return code 15 The log file in reference is very old and is not on the Master PG server in pg_xlogs and the "last completed transaction" can't be right either. Is this a bug or it is something we are doing wrong? Thanks, Keaton psql (PostgreSQL) 8.3.7 contains support for command-line editing RHEL 5 64 Bit Linux ourservername 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
Re: [GENERAL] dynamic insert in plpgsql
what is that "(t" in the SELECT there for ? or is it just typo, or something missing/etc ? -- 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] dynamic insert in plpgsql
On Fri, Jan 8, 2010 at 2:58 PM, Sam Mason wrote: > Yup, this thing is a bit fiddly. Try: > > http://archives.postgresql.org/pgsql-general/2009-09/msg01176.php I searched for it, but didn't stumble upon that one. Thanks. -- 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] dynamic insert in plpgsql
On Fri, Jan 08, 2010 at 02:55:53PM +, Grzegorz Jaaakiewicz wrote:
> Is there any nice way to do something like that in plpgsql:
>
> EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
>
> It would probably work, but some values are NULL, and plpgsql
> interpreter just puts empty space there. So I get ('1',2,3,,,); Which
> obviously is confusing INSERT.
Yup, this thing is a bit fiddly. Try:
http://archives.postgresql.org/pgsql-general/2009-09/msg01176.php
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dynamic insert in plpgsql
Is there any nice way to do something like that in plpgsql:
EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
It would probably work, but some values are NULL, and plpgsql
interpreter just puts empty space there. So I get ('1',2,3,,,); Which
obviously is confusing INSERT.
thx.
--
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] pg.dropped
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes: > create table test (id serial primary key, t1 text, t2 text); > create function myhash(test) returns text as 'select md5($1::text)' language > sql immutable; > create index myhash on test( myhash(test) ); > alter table test add t3 text; > alter table test drop t3; > insert into test(t1,t2) select 'foo', 'bar'; Mph. That seems to be an unhandled case that we ought to handle. 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
Re: [GENERAL] Server name in psql prompt
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Is there any way of getting psql to display the name of the > currently-connected server in its prompt, and perhaps a custom string > identifying e.g. a disc set, without having to create a psqlrc file on > every client system that's got a precompiled psql installed? Sure, use backticks to get what you want into there. For example, here's one of my common prompts: \set PROMPT1 '%...@%`hostname`:%>%R%#%x%x%x ' I once had a client that needed something more than that, so I wrote a quick shell script that outputted the info on a single line and then called the script inside the backticks. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201001080924 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAktHQIcACgkQvJuQZxSWSsgo6QCg5/4Rtx5Jnoso+i9P6+cph+1e do8AoIVqlXg8u8Eb8NtPWm+Y2y+sYyfB =gmZS -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] check the execution status of stored procedure
Yan Cheng Cheok writes:
> Currently, I try to call a stored procedure with void returned type.
> PGresult *res = PQexec(this->getConnection(), "SELECT * FROM
> create_tables()");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> {
> PQclear(res);
> return false;
> }
> Since the returned type of stored procedure is void, I will always fall in to
> the block
> if (PQresultStatus(res) != PGRES_COMMAND_OK) {}
A successful SELECT command will return PGRES_TUPLES_OK, not
PGRES_COMMAND_OK. Whether the function result is void or not doesn't
change that.
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
Re: [GENERAL] Rows missing from table despite FK constraint
Konrad Garus writes: > 2010/1/8 Tom Lane : >> Do you know that the rows disappeared recently? > Yes. They are present in dump from 9 PM and missing from dump from 1 > AM. It must've happened within this 4-hour window. Hm. It would be interesting to see if you can find the place where the rows had been and dump it with pg_filedump http://sources.redhat.com/rhdb/ You can look at the ctid column of the rows that are adjacent to the missing ones according to your older dump, and then dump out those blocks (I recommend -i -f style). 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
Re: [GENERAL] Rows missing from table despite FK constraint
2010/1/8 Tom Lane : > Do you know that the rows disappeared recently? Yes. They are present in dump from 9 PM and missing from dump from 1 AM. It must've happened within this 4-hour window. -- Konrad Garus -- 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] Server name in psql prompt
On Fri, 2010-01-08 at 11:20 +, Mark Morgan Lloyd wrote: > Is there any way of getting psql to display the name of the > currently-connected server in its prompt, and perhaps a custom string > identifying e.g. a disc set, without having to create a psqlrc file on > every client system that's got a precompiled psql installed? It's a reasonable request but PostgreSQL databases don't have specific names. You have to set up the logic yourself. -- Simon Riggs www.2ndQuadrant.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] Rows missing from table despite FK constraint
Konrad Garus writes: > 2010/1/8 Alban Hertroys : >> I get the impression the data you lost and the data around it hasn't been >> written to in a long time; it wouldn't surprise me if your problem would >> have been caused by a bad sector on a disk, but that depends on how reliable >> your storage is set up to be. > You are correct about the first point. It's a write-only table with > thousands of inserts daily, and the lost rows were written 7 months > ago. Do you know that the rows disappeared recently? 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
Re: [GENERAL] Server name in psql prompt
On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote: > Is there any way of getting psql to display the name of the > currently-connected server in its prompt, and perhaps a custom string > identifying e.g. a disc set, without having to create a psqlrc file on > every client system that's got a precompiled psql installed? what exactly is the problem with distributing your own .psqlrc? for me it's one of the first things that I do - I setup environment. 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] Rows missing from table despite FK constraint
2010/1/8 Alban Hertroys : > You seem to have lost the actual data, not the index entries pointing to it, > or a sequential scan (eg. pg_dump) would still have found your rows. I agree. > What kind of file-system is the affected table on? - and while we're at it, > what OS/Distribution and version? Is your data on some kind of RAID array? If > so, what type (hardware/software, RAID type)? It's ext3 on a hardware RAID1. The array is in perfect condition, according to its diag tool. The OS is Ubuntu 8.04. The exact PG version is 8.3.8. > I get the impression the data you lost and the data around it hasn't been > written to in a long time; it wouldn't surprise me if your problem would have > been caused by a bad sector on a disk, but that depends on how reliable your > storage is set up to be. You are correct about the first point. It's a write-only table with thousands of inserts daily, and the lost rows were written 7 months ago. -- Konrad Garus -- 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] Server name in psql prompt
--- On Fri, 8/1/10, Mark Morgan Lloyd Is there any way of getting psql to > display the name of the currently-connected server in its > prompt, and perhaps a custom string identifying e.g. a disc > set, without having to create a psqlrc file on every client > system that's got a precompiled psql installed? You could use the psql -v option to set the PROMPT variables (or set them as ENV) see: http://www.postgresql.org/docs/8.3/static/app-psql.html http://www.postgresql.org/docs/8.3/static/app-psql.html#APP-PSQL-PROMPTING -- 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] Server name in psql prompt
2010/1/8 Mark Morgan Lloyd > Is there any way of getting psql to display the name of the > currently-connected server in its prompt, and perhaps a custom string > identifying e.g. a disc set, without having to create a psqlrc file on every > client system that's got a precompiled psql installed? > > No. > I've just come close to dropping a table that would have been embarrassing > because I couldn't see which server an instance of psql was talking to. Now > obviously that's due to lackwittedness on my part and it could be cured by > installing psqlrc files- but this might not be a viable option since it > means chasing down every psql binary that's been installed on the LAN in an > attempt to protect users from self-harm: far nicer if the default psql > prompt could be loaded from the server. > > Not every binary; every user profile. If you need it, maybe employ some company-wide user profile scripts. That's not so hard if you use Linux/Unix environment; just use /etc/rpofile to enforce a common policy. > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or colleagues] > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
[GENERAL] Server name in psql prompt
Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? I've just come close to dropping a table that would have been embarrassing because I couldn't see which server an instance of psql was talking to. Now obviously that's due to lackwittedness on my part and it could be cured by installing psqlrc files- but this might not be a viable option since it means chasing down every psql binary that's been installed on the LAN in an attempt to protect users from self-harm: far nicer if the default psql prompt could be loaded from the server. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Rows missing from table despite FK constraint
On 7 Jan 2010, at 11:12, Konrad Garus wrote: > Hello, > > We use PG 8.3. We use pg_dump and pg_restore overnight to create > ... You seem to have lost the actual data, not the index entries pointing to it, or a sequential scan (eg. pg_dump) would still have found your rows. > Do you have any ideas on how it could possibly happen? What research > could help find the root cause and fix the database? What kind of file-system is the affected table on? - and while we're at it, what OS/Distribution and version? Is your data on some kind of RAID array? If so, what type (hardware/software, RAID type)? I get the impression the data you lost and the data around it hasn't been written to in a long time; it wouldn't surprise me if your problem would have been caused by a bad sector on a disk, but that depends on how reliable your storage is set up to be. Bad memory is another typical cause of corruption, but not likely in your case. And of course there could be a bug in PG; are you up to date on the minor versions? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b47130010732637119309! -- 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.dropped
Full test case, reproduced in 8.4.2 on two different hosts create table test (id serial primary key, t1 text, t2 text); create function myhash(test) returns text as 'select md5($1::text)' language sql immutable; create index myhash on test( myhash(test) ); alter table test add t3 text; alter table test drop t3; insert into test(t1,t2) select 'foo', 'bar'; PS. I realise that marking of CAST (rowtype as text) as immutable may be not safe. But this behaviour is probably a bug anyway. 2010/1/7 Tom Lane > =?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes: > > INSERT INTO thetable ( ... ) VALUES ( ... ); > > ERROR: table row type and query-specified row type do not match > > If you want any help with this you need to show a *complete* example > of how to produce this failure. > >regards, tom lane > -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
[GENERAL] how much left for restore?
Is there a way to know/estimate how much is left to complete a restore? It would be enough just knowing which part of the file is being restored (without causing too much extra IO, that will definitively put my notebook on its knee). Next time I try a restore on this box is there anything I could tweak in pg config to make it faster? For dev only... could I just stop the dev server, copy the *files* on flash and mount them on the notebook? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Return Single Row Result After Inserting (Stored Procedure)
In response to Yan Cheng Cheok : > Hello all, > > I have the following procedure. I wish it will return a single row > result to caller, after I insert the value (as the row contains > several auto generated fields), without perform additional SELECT > query. > > According to > http://wischner.blogspot.com/2009/03/creating-stored-procedure-function.html, > my guess is that, I need to use SETOF. However, pgAdmin doesn't allow > me to enter "SETOF" in "Return Type". > > However, it let me enter "lot" (lot is the name of the table) > > May I know how can I modified the following function, to let it returns my > newly inserted row? > > CREATE OR REPLACE FUNCTION create_lot(text, text, text, text, text, text) > RETURNS lot AS > $BODY$DECLARE > configurationFile ALIAS FOR $1; > operatorName ALIAS FOR $2; > machineName ALIAS FOR $3; > BEGIN > INSERT INTO lot(configuration_file, operator_name, machine_name) > VALUES(configurationFile, operatorName, machineName); > END;$BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres; You have defined a function with 6 input-parameters, but inside the function there are only 3 used. Why? You can rewrite your function, simple example: -- create a simple table with 2 columns test=# create table foo (col1 int, col2 text); CREATE TABLE -- create a simple function test=*# create or replace function insert_foo(int, text) returns foo as $$insert into foo values ($1, $2) returning *; $$language sql; CREATE FUNCTION -- use that function test=*# select * from insert_foo(1, 'test') ; col1 | col2 --+-- 1 | test (1 row) -- check, if our table contains the new record test=*# select * from foo; col1 | col2 --+-- 1 | test (1 row) Yeah! For such simple task you can use language SQL instead ig pl/pgsql. > > Thanks and Regards > Yan Cheng CHEOK > > p/s May I know what is the purpose of "COST 100"? It is a hint for the planner to calculate the costs for the function. You can omit this parameter. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
