[GENERAL] Changing the location of the default data directory on PG 9.6.6 (CentOS 7)?
I'm trying to identify which postgresql.conf file I should be editing, in order to change the default database files location for Postgres 9.6.6, when installed on CentOS 7.x/ Is the bet method for changing the default data directory at the time of database init, to include the $PGDATA variable at initialization, such as: su - postgres -c '/usr/pgsql-9.6/bin/initdb --pgdata=$PGDATA', where $PGDATA is the directory path that I want the psql database files to reside?
Re: [GENERAL] UUIDs & Clustered Indexes
Tom, Ah, that makes more sense. Thank you very much! On Tue, Aug 30, 2016 at 9:15 AM, Tom Lane wrote: > Luke Gordon writes: > > However, according to a message on this mailing list, Postgres doesn't > have > > clustered indexes: > > "But Postgres doesn't _have_ clustered indexes, so that article doesn't > > apply at all. The other authors appear to have missed this important > point." > > https://www.postgresql.org/message-id/56798352.7060902%40uchicago.edu > > > But, doing a quick check, it appears Postgres does indeed have a > mechanism > > for a clustered index: > > https://www.postgresql.org/docs/9.5/static/sql-cluster.html > > CLUSTER just does a one-time sort to put the table into index order. > There is no mechanism that would cause subsequent insertions of new keys > to respect that ordering, so it's pretty much irrelevant to the argument > about whether new UUID keys need to be generated in some ordered fashion. > > Do you actually *need* UUID keys, and if so why? A plain old bigint > column is smaller, cheaper to index, and the natural mechanism for > generating it (ie a sequence) will tend to preserve ordering for free. > > regards, tom lane >
[GENERAL] UUIDs & Clustered Indexes
I'm trying to decide on which UUID generator to use for my Postgres database, and I've narrowed it down to gen_random & uuid_generate_v1mc. There's a fascinating article that discusses performance implications between gen_random_uuid & uuid_generate_v1mc: https://www.starkandwayne.com/blog/uuid-primary-keys-in-postgresql/ TL;DR, the article suggests: "Random produces very fragmented inserts that destroy tables. Use uuid_generate_v1mc() [instead].." However, according to a message on this mailing list, Postgres doesn't have clustered indexes: "But Postgres doesn't _have_ clustered indexes, so that article doesn't apply at all. The other authors appear to have missed this important point." https://www.postgresql.org/message-id/56798352.7060902%40uchicago.edu But, doing a quick check, it appears Postgres does indeed have a mechanism for a clustered index: https://www.postgresql.org/docs/9.5/static/sql-cluster.html <https://www.postgresql.org/docs/9.5/static/sql-cluster.html>So, does anyone know which is best? Or are the performance differences so minute they'd only matter in extremely unique circumstances? Regards, Luke Gordon PS I attempted to reply to the above thread, but downloading the "raw" link resulted in a basic auth challenge. If I entered my mailing list credentials, I'd just get rechallenged with basic auth...
[GENERAL] Row-level Security vs Application-level authz
I'm trying to understand the extent that row level security in postgresql 9.5 may replace, or augment, application-level access control. I have a fully implemented application-level access control policy. It's not clear to me how I will integrate or replace it with RLS. Craig Ringer mentioned in a blog post: "Most importantly, row-security is pluggable – in addition to looking policies up from the system catalogs, it’s also possible to use a policy hook to supply arbitrary policy from extensions. " It seems that my options will be to record authorization into the catalog or write an extension? Thanks Darin
[GENERAL] Ransomware article
TheRegister is running an article about someone breaking into a dbase, taking control of the encryption key, and 6 or so months later demanding ransom from the owner of the dbase. http://www.theregister.co.uk/2015/02/03/web_ransomware_scum_now_lay_waste_to_your_backups/ Anyone want to comment on this? In general, or PostgreSQL in particular? Gord -- 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] Problem with REFERENCES on INHERITS
On 02/02/15 10:11 AM, Tom Lane wrote: > If you did "select * from only primate" you would see that there is no > such row in the parent table, which is what the foreign key is being > enforced against. Thanks. That does a lot to clarify it. -Will -- 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] Problem with REFERENCES on INHERITS
On 02/02/15 12:11 AM, David G Johnston wrote: > William Gordon Rutherdale wrote >> My problem: could someone please explain the semantics and why this >> behaviour makes sense -- or is it a design error or bug? > I didn't read your post in depth but I suspect you have not read and > understood the limitations documented in section 5.8.1 > > http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html Okay, thanks. The caveats section says this: A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. It also says that this 'deficiency' may be fixed in some future release. Well, I guess that covers it. I still find it curious: the particular form in which it manifests itself. Instead of simply not performing the reference check, it over-does it on the derived table. So this statement: INSERT INTO banana_stash(primate_id, qty) VALUES (1, 17); Resulted in this error: ERROR: insert or update on table "banana_stash" violates foreign key constraint "banana_stash_primate_id_fkey" DETAIL: Key (primate_id)=(1) is not present in table "primate". How am I to interpret this? A select * from primate shows that a row with primate.id exists, yet the error message indicates that it doesn't. -Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with REFERENCES on INHERITS
Hi.
I have encountered a problem with references when using INHERITS (on
Postgres 9.1/9.2). Could someone please explain why this occurs.
Consider this example.
CREATE TABLE primate
(
id SERIAL PRIMARY KEY,
name TEXT,
tale TEXT
);
CREATE TABLE chimp
(
human_friend TEXT
) INHERITS(primate);
INSERT INTO chimp(name, tale, human_friend) VALUES
('Cheetah', 'Curly', 'Tarzan');
INSERT INTO primate(name, tale) VALUES
('King Julien', 'Move it');
SELECT * FROM primate;
==>
id |name | tale
+-+-
2 | King Julien | Move it
1 | Cheetah | Curly
(2 rows)
CREATE TABLE banana_stash
(
id SERIAL,
primate_id INTEGER REFERENCES primate(id),
qty INTEGER
);
INSERT INTO banana_stash(primate_id, qty) VALUES
(1, 17);
==>
ERROR: insert or update on table "banana_stash" violates foreign key
constraint "banana_stash_primate_id_fkey"
DETAIL: Key (primate_id)=(1) is not present in table "primate".
INSERT INTO banana_stash(primate_id, qty) VALUES
(2, 22);
==>
INSERT 0 1
SELECT * FROM banana_stash;
==>
id | primate_id | qty
++-
2 | 2 | 22
(1 row)
My problem: could someone please explain the semantics and why this
behaviour makes sense -- or is it a design error or bug?
To sum up the issue:
- I insert into the derived table (chimp) and get id 1
- I insert into the base table (primate) and get id 2
- I have a foreign key constraint in banana_stash to the
base table p.k. primate(id)
- inserting to banana_stash with reference to id 2 is okay
- inserting to banana_stash with reference 1 gives error
- both ids 1 and 2 in table primate are supposed to be valid
So why does the one case give an error when the other does not?
Also, is there a way to solve this problem (i.e. remove the error)
without simply chopping out the REFERENCES clause from banana_stash?
-Will
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Download specific Postgres.App version
On the postgresapp.com you can only download the latest version of Postgres for the Mac. Is it possible to download a specific version? Thanks, GTG -- Gordon Ross -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrade from 9.1 to 9.2 fails due to unlogged table?
I have a 9.1.3 instance (Redhat 5) with some unlogged tables. I did the
following steps:
1. pg_basebackup to create a base.tar
2. Used the base.tar plus the WALs required by the backup to restore the db
to another 9.1.3 server. This went fine, except at the end of the recovery
I got this error (file exists).
77402013-03-28 09:44:16 MDT [2013-03-28 09:38:43 MDT] [29] LOG: archive
recovery complete
77522013-03-28 09:44:16 MDT [2013-03-28 09:38:48 MDT] [1] LOG:
checkpoint starting: end-of-recovery immediate wait
77522013-03-28 09:44:25 MDT [2013-03-28 09:38:48 MDT] [2] LOG:
checkpoint complete: wrote 8213 buffers (1.6%); 0 transaction log file(s)
added, 0 removed, 0 recycled; write=7.361 s, sync=1.563 s, total=8.992 s;
sync files=2492, longest=0.069 s, average=0.000 s
*77402013-03-28 09:44:25 MDT [2013-03-28 09:38:43 MDT] [30] FATAL:
could not create file "base/886355/29806058": File exists*
77372013-03-28 09:44:25 MDT [2013-03-28 09:38:42 MDT] [1] LOG: startup
process (PID 7740) exited with exit code 1
77372013-03-28 09:44:25 MDT [2013-03-28 09:38:42 MDT] [2] LOG:
terminating any other active server processes
In a minute, started up again, and it came up without errors, and we ran for
a couple of days with no errors noted.
3. I then did a pg_upgrade to 9.2.3. All went well, until...
Linking user relation files
/var/lib/pgsql/9.1/data/base/16388/15963579
error while creating link for relation "stage.classification"
("/var/lib/pgsql/9.1/data/base/16388/15963579" to
"/var/lib/pgsql/9.2/data/base/16433/15963579"): No such file or directory
Failure, exiting
I checked and it's true, the file does not exist. Now I can't proceed and
the 9.1 data is not usable. I looked at the original database from which
the copy was made, and the relation in question is an UNLOGGED table. This
suggests that there's some consideration for binary recovery. I don't care
about the unlogged table data, but I do need the table definition. We use a
few dozen unlogged tables. Any ideas?
Thanks,
Gordon
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-2-fails-due-to-unlogged-table-tp5750194.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Upgrade from 9.1 to 9.2 fails due to unlogged table?
I repeated the entire process, and I have a few clarifications.
When I said the db seemed fine after the restore, I was wrong. I could do a
\d on an unlogged table, but when I selected count(*) from any, that
resulted in an error like "could not open file "base/16388/15963587": No
such file or directory". So the database was definitely not happy after the
restore.
Also, I was wrong when I said I "couldn't go back" after the failed upgrade.
I read the output again, and yes I was able to restart 9.1 after renaming
pg_control.old.
Next, I tried dropping and recreating all my UNLOGGED tables, as logged
tables. On my first try, I forgot a few tables, and the pg_upgrade error
this time was:
Linking user relation files
/var/lib/pgsql/9.1/data/base/16389/29807980
error while creating link for relation "pg_toast.pg_toast_15949256_index"
("/var/lib/pgsql/9.1/data/base/16389/29807980" to
"/var/lib/pgsql/9.2/data/base/16434/15949261"): No such file or directory
Failure, exiting
I realized my mistake, and recreated the remaining unlogged tables. After
that the pg_upgrade worked perfectly.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-2-fails-due-to-unlogged-table-tp5750194p5750207.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] could not access status of transaction 1118722281
Turns out this was most likely the pg_upgrade bug. In our case, I was able to dump and recreate the table in question. Since then, this has been made public: http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix -- View this message in context: http://postgresql.1045698.n5.nabble.com/could-not-access-status-of-transaction-1118722281-tp4283137p4293709.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] could not access status of transaction 1118722281
Running 9.0.2 on Centos. I just discovered this in my production error log. Starting about 45 minutes ago, I got 70 of these, within 2 seconds: 28652 2011-04-04 21:47:29 EDT [33]WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "pg_toast_49338181" page 16820 These warnings were immediately proceeded by this, which has continuously repeated every 15 seconds since then: 8895 2011-04-04 22:15:28 EDT [1]ERROR: could not access status of transaction 1118722281 8895 2011-04-04 22:15:28 EDT [2]DETAIL: Could not open file "pg_clog/042A": No such file or directory. 8895 2011-04-04 22:15:28 EDT [3]CONTEXT: automatic vacuum of table "mcore.pg_toast.pg_toast_48975830" I checked and the pg_clog files start at 04BF and run through 0A57 (1,433 files) Any help would be greatly appreciated. Gordon -- View this message in context: http://postgresql.1045698.n5.nabble.com/could-not-access-status-of-transaction-1118722281-tp4283137p4283137.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] walreceiver getting bad data?
It's 9.0.2 on Centos -- View this message in context: http://postgresql.1045698.n5.nabble.com/walreceiver-getting-bad-data-tp3329916p3330573.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] walreceiver getting bad data?
I'm getting an error like this in the log a couple times a day on on my hot standby server. Any suggestions? 23964 2011-01-04 05:23:00 EST [47]LOG: invalid record length at 6E53/46E8A010 23535 2011-01-04 05:23:00 EST [2]FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/data23/wal_sync/00016E530046': No such file or directory 23964 2011-01-04 05:23:00 EST [48]LOG: invalid record length at 6E53/46E8A010 cp: cannot stat `/data23/wal_sync/00016E530046': No such file or directory 2486 2011-01-04 05:23:00 EST [1]LOG: streaming replication successfully connected to primary Thanks, Gordon -- View this message in context: http://postgresql.1045698.n5.nabble.com/walreceiver-getting-bad-data-tp3329916p3329916.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] seg fault crashed the postmaster
I'm putting this on this thread, since it could be related to the issue. I'm now seeing this in the log on the HSB/SR server. It's happened about 4 times in the past 2 days. 23964 2011-01-04 05:23:00 EST [47]LOG: invalid record length at 6E53/46E8A010 23535 2011-01-04 05:23:00 EST [2]FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/data23/wal_sync/00016E530046': No such file or directory 23964 2011-01-04 05:23:00 EST [48]LOG: invalid record length at 6E53/46E8A010 cp: cannot stat `/data23/wal_sync/00016E530046': No such file or directory 2486 2011-01-04 05:23:00 EST [1]LOG: streaming replication successfully connected to primary 23964 2011-01-04 15:47:59 EST [49]LOG: invalid record length at 6E6B/F8222010 2486 2011-01-04 15:47:59 EST [2]FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/data23/wal_sync/00016E6B00F8': No such file or directory 23964 2011-01-04 15:48:00 EST [50]LOG: invalid record length at 6E6B/F8222010 cp: cannot stat `/data23/wal_sync/00016E6B00F8': No such file or directory The word FATAL sounds very ominous. -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3328138.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] seg fault crashed the postmaster
Unfortunately it's now impossible to say how many were updated, as they get deleted by another process later. I may be able to restore part of a dump from 2 days ago on another machine, and get some counts from that, assuming I have the disk space. I'll work on that. I do not believe there could have been more than 2 concurrent updates because the app is limited to 2 threads, but I will verify that with the developer. -gordon On Fri, Dec 31, 2010 at 3:43 PM, Tom Lane-2 [via PostgreSQL] < ml-node+3323971-508486184-56...@n5.nabble.com > wrote: > > > No luck here either. A couple further questions --- do you think it's > likely that you could have been over 1 matches, ie, the LIMIT > actually kicked in? Also, could there have been more than two > concurrent updates acting on the same rows? > > -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3324015.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] seg fault crashed the postmaster
The number of matching rows on these queries is anything from 0 to 1. I don't think I can tell how many would have matched on the ones that crashed. Although I suspect it would have been toward the 1 end. I've been trying to get a reproducable test case with no luck so far. I assume you can now see the plan? I uploaded it twice, once via gmail and once on Nabble. Here are all the non-default settings: > listen_addresses = '*' > max_connections = 450 > authentication_timeout = 20s > shared_buffers = 18GB > work_mem = 200MB > maintenance_work_mem = 8GB > shared_preload_libraries = 'auto_explain' > wal_level = hot_standby > synchronous_commit = off > wal_buffers = 8MB > commit_siblings = 1 > checkpoint_segments = 256 > checkpoint_warning = 5min > archive_mode = on > archive_command = 'cp %p /var/lib/pgsql/wal/%f.wrk; mv /var/lib/pgsql/wal/%f.wrk /var/lib/pgsql/wal/%f' > max_wal_senders = 1 > cpu_tuple_cost = 0.003 > cpu_index_tuple_cost = 0.001 > cpu_operator_cost = 0.0005 > effective_cache_size = 52GB > default_statistics_target = 200 > log_directory = '/var/log/postgres' > log_filename = 'pg%d.log' > log_min_duration_statement = 7min > log_line_prefix = '%p %u %r %t [%l]' > log_lock_waits = on > log_temp_files = 0 > track_functions = pl# none, pl, all > log_autovacuum_min_duration = 5min > autovacuum_vacuum_scale_factor = 0.1 > autovacuum_analyze_scale_factor = 0.03 > autovacuum_freeze_max_age = 15# 1,500,000,000 > autovacuum_vacuum_cost_delay = -1 > temp_tablespaces = 'ts03,ts04,ts05,ts06,ts07,ts08,ts09,ts10,ts11,ts12,ts13,ts14,ts15,ts16,ts17,ts18,ts19,ts20,ts21,ts22,ts23,ts24,ts25,ts26,ts27,ts28,ts29,ts30,ts31,ts32,ts33,ts34,ts35,ts36,ts37,ts38' > vacuum_freeze_min_age = 5 # 500,000,000 > vacuum_freeze_table_age = 13 # 1,300,000,000 > bytea_output = 'escape' > deadlock_timeout = 5s > standard_conforming_strings = on > custom_variable_classes = 'auto_explain' > auto_explain.log_min_duration = -1 # Remember this means for everybody! > auto_explain.log_analyze = off ## DANGER! Don't set log_analyze to true unless you know what you're doing! > auto_explain.log_verbose = off > auto_explain.log_nested_statements = on On Fri, Dec 31, 2010 at 2:49 PM, Tom Lane-2 [via PostgreSQL] < ml-node+3323935-1680610224-56...@n5.nabble.com > wrote: > > So I'm pretty sure that what we're dealing with is a case of the plan > freeing a transient tuple datum sooner than it should. But the toy case > I tried here didn't fail, so evidently I'm not close enough to the plan > you're actually using. Still need to see that EXPLAIN output. It'd be > helpful also to know what nondefault configuration settings you're > using, especially work_mem and planner-related settings. Also, do you > have an idea of how many rows might've matched the WHERE conditions? > > > -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323959.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] seg fault crashed the postmaster
Maybe it doesn't work from gmail. I'll try uploading from here. http://postgresql.1045698.n5.nabble.com/file/n3323933/plan.txt plan.txt -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323933.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] seg fault crashed the postmaster
Yes that query does take 30 or 90 secs. I'm pretty sure it was blocking on its twin update running concurrently. However I'm not really sure how to identify what "transaction 1283585646" was. Enclosed is the query plan -- 21000 lines -gordon I tried to replicate the problem here without success, which suggests to > me that maybe it's plan-dependent. Can you show what EXPLAIN gives you > for the troublesome query? > > Another point here is that your logs suggest that the query blocked > behind the previous updater of the row for a very long time --- 90 sec > in one case, 30 sec in another. Is that the typical runtime for one of > these updates? If not, can you identify what the blocking query was? > If it's something different from another instance of the identical > update, it'd be useful to know what. > > -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323915.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] seg fault crashed the postmaster
Sorry, I left that out. Yeah, I wondered that too, since these tables do not use toast. CREATE TYPE message_status_enum AS ENUM ( 'V', 'X', 'S', 'R', 'U', 'D' ); On Fri, Dec 31, 2010 at 12:38 PM, Tom Lane-2 [via PostgreSQL] < ml-node+3323859-1425181809-56...@n5.nabble.com > wrote: > Hmmm ... what is "message_status_enum"? Is that an actual enum type, or > some kind of domain over text or varchar? If it's an enum, I'm a bit > baffled where the "corrupt compressed data" message could have come from. > > regards, tom lane > > -- > -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323888.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] seg fault crashed the postmaster
Here is the ddl for the tables in question. There are foreign keys to other tables that I omitted. http://postgresql.1045698.n5.nabble.com/file/n3323804/parts.sql parts.sql -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323804.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] seg fault crashed the postmaster
Interesting. That's exactly what we have been doing -- trying to update the same rows in multiple txns. For us to proceed in production, I will take steps to ensure we stop doing that, as it's just an app bug really. The table in question -- v_messages -- is an empty base table with 76 partitions, with a total of 2.8 billion rows. Let me summarize what I see as the key facts here: (All problems have come from the UPDATE query, all identical except for different "author_id" values.) 1. We did a "link" upgrade Wed night, from 844 to 902 so the upgrade happened in place, no data files were copied. 2. The 1st error was "compressed data is corrupt" at 18:16 3. We got 2 seg fault crashes before turning on cores and getting a 3rd crash with the stack trace. 4. We then got a " invalid memory alloc request size 18446744073449177092" at 23:50. This was an ERROR, not a crash. At this point, is it your suspicion that there is a code bug in 9.0.2, rather than corrupt data? I will post the schema and then work on a test case. -gordon On Fri, Dec 31, 2010 at 8:34 AM, Tom Lane-2 [via PostgreSQL] < ml-node+3323712-1368244686-56...@n5.nabble.com > wrote: > > Hmm. This suggests that there's something wrong in the EvalPlanQual > code, which gets invoked when there are concurrent updates to the same > row (ie, the row this UPDATE is trying to change is one that was changed > by some other transaction since the query started). That stuff got > rewritten rather thoroughly for 9.0, so the idea of a new bug there > isn't exactly surprising. But it's going to be hard to find without > a test case. Can you show us the full schema for this table and all > the queries that execute against it up till the point of the failure? > (Turning on log_statement across all sessions would help collect that > info, if you don't have it already.) > > -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323796.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] seg fault crashed the postmaster
Stack trace: #0 0x0031a147c15c in memcpy () from /lib64/libc.so.6 #1 0x00450cb8 in __memcpy_ichk (tuple=0x7fffb29ac900) at /usr/include/bits/string3.h:51 #2 heap_copytuple (tuple=0x7fffb29ac900) at heaptuple.c:592 #3 0x00543d4c in EvalPlanQualFetchRowMarks (epqstate=0x3cd85ab8) at execMain.c:1794 #4 0x005440db in EvalPlanQual (estate=0x1e0db420, epqstate=0x3cd85ab8, relation=, rti=4, tid=0x7fffb29aca20, priorXmax=) at execMain.c:1401 #5 0x005592eb in ExecUpdate (node=0x3cd85a28) at nodeModifyTable.c:527 #6 ExecModifyTable (node=0x3cd85a28) at nodeModifyTable.c:748 #7 0x00545953 in ExecProcNode (node=0x3cd85a28) at execProcnode.c:359 #8 0x00544881 in ExecutePlan (queryDesc=0x1e727990, direction=1039265768, count=0) at execMain.c:1190 #9 standard_ExecutorRun (queryDesc=0x1e727990, direction=1039265768, count=0) at execMain.c:280 #10 0x2ab002c0f2b5 in explain_ExecutorRun (queryDesc=0x1e727990, direction=ForwardScanDirection, count=0) at auto_explain.c:203 #11 0x005f9c81 in ProcessQuery (plan=0x2112ad60, sourceText=0x1b3e59e0 "update v_messages set status = 'S', updated_on = now() where id in (select id from v_messages where author_id = 33138761 and status != 'S' limit 1)", params=, dest=0x2112ae40, completionTag=0x7fffb29ace20 "") at pquery.c:197 #12 0x005f9e99 in PortalRunMulti (portal=0x1b32aed0, isTopLevel=1 '\001', dest=0x2112ae40, altdest=0x2112ae40, completionTag=0x7fffb29ace20 "") at pquery.c:1268 #13 0x005fa965 in PortalRun (portal=0x1b32aed0, count=9223372036854775807, isTopLevel=1 '\001', dest=0x2112ae40, altdest=0x2112ae40, completionTag=0x7fffb29ace20 "") at pquery.c:822 #14 0x005f7455 in exec_simple_query ( query_string=0x1b3e59e0 "update v_messages set status = 'S', updated_on = now() where id in (select id from v_messages where author_id = 33138761 and status != 'S' limit 1)") at postgres.c:1058 #15 0x005f7d14 in PostgresMain (argc=, argv=, username=) at postgres.c:3929 #16 0x005c7ce5 in ServerLoop () at postmaster.c:3555 #17 0x005c89ec in PostmasterMain (argc=5, argv=0x1b31ea00) at postmaster.c:1092 #18 0x005725fe in main (argc=5, argv=) at main.c:188 On Thu, Dec 30, 2010 at 7:32 PM, Tom Lane-2 [via PostgreSQL] < ml-node+3323177-1417305259-56...@n5.nabble.com > wrote: > Gordon Shannon <[hidden > email]<http://user/SendEmail.jtp?type=node&node=3323177&i=0>> > writes: > > I'd love to send you a stack trace. Any suggestions on how to get one? > It > > has since happened again, on the same update command, so I'm guessing I > can > > repeat it. > > > http://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend > > regards, tom lane > > -- > Sent via pgsql-general mailing list ([hidden > email]<http://user/SendEmail.jtp?type=node&node=3323177&i=1>) > > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > -- > View message @ > http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323177.html > > To unsubscribe from seg fault crashed the postmaster, click > here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=3323117&code=Z29yZG8xNjlAZ21haWwuY29tfDMzMjMxMTd8LTEwNjcwMjEwNDQ=>. > > -- If I had more time, I could have written you a shorter letter. (Blaise Pascal) -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323277.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] seg fault crashed the postmaster
I'd love to send you a stack trace. Any suggestions on how to get one? It has since happened again, on the same update command, so I'm guessing I can repeat it. On Thu, Dec 30, 2010 at 6:52 PM, Tom Lane-2 [via PostgreSQL] < ml-node+3323151-436577542-56...@n5.nabble.com > wrote: > Gordon Shannon <[hidden > email]<http://user/SendEmail.jtp?type=node&node=3323151&i=0>> > writes: > > Running Centos, just upgraded our production db from 8.4.4 to 9.0.2 last > > night. About 20 hours later, an update statement seg faulted and crashed > > > the server. This is a typical update that has worked fine for a long > time. > > Could we see a stack trace from that? Or at least a self-contained > test case? > > regards, tom lane > > -- > Sent via pgsql-general mailing list ([hidden > email]<http://user/SendEmail.jtp?type=node&node=3323151&i=1>) > > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > -- > View message @ > http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323151.html > To unsubscribe from seg fault crashed the postmaster, click > here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=3323117&code=Z29yZG8xNjlAZ21haWwuY29tfDMzMjMxMTd8LTEwNjcwMjEwNDQ=>. > > -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323171.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] seg fault crashed the postmaster
Hi, Running Centos, just upgraded our production db from 8.4.4 to 9.0.2 last night. About 20 hours later, an update statement seg faulted and crashed the server. This is a typical update that has worked fine for a long time. 20898 datafeed (58628) 2010-12-30 19:28:14 EST [103]LOG: process 20898 acquired ShareLock on transaction 1286738762 after 90174.969 ms 20898 datafeed (58628) 2010-12-30 19:28:14 EST [104]STATEMENT: update v_messages set status = 'S', updated_on = now() where id in (select id from v_messages where author_id = 34409854 and status != 'S' limit 1) 5802 2010-12-30 19:28:14 EST [4]LOG: server process (PID 20898) was terminated by signal 11: Segmentation fault 5802 2010-12-30 19:28:14 EST [5]LOG: terminating any other active server processes 15426 pipeline (36834) 2010-12-30 19:28:14 EST [1]WARNING: terminating connection because of crash of another server process15426 pipeline 10.10.11.54(36834) 2010-12-30 19:28:14 EST [2]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server pro cess exited abnormally and possibly corrupted shared memory. One hint that something might be wrong was this: 18235 datafeed (44228) 2010-12-30 18:16:37 EST [11]LOG: process 18235 acquired ShareLock on transaction 1285952031 after 29966.703 ms 18235 datafeed (44228) 2010-12-30 18:16:37 EST [12]STATEMENT: update v_messages set status = 'S', updated_on = now() where id in (select id from v_messages where author_id = 25301995 and status != 'S' limit 1) 18235 datafeed (44228) 2010-12-30 18:16:43 EST [13]ERROR: compressed data is corrupt 18235 datafeed (44228) 2010-12-30 18:16:43 EST [14]STATEMENT: update v_messages set status = 'S', updated_on = now() where id in (select id from v_messages where author_id = 25301995 and status != 'S' limit 1) How concerned should I be? Thanks! Gordon -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3323117.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Cannot unsubscribe
> -Original Message- > From: Adrian Klaver [mailto:adrian.kla...@gmail.com] > Sent: 21 December 2010 20:36 > To: pgsql-general@postgresql.org > Cc: William Gordon Rutherdale (rutherw) > Subject: Re: [GENERAL] Cannot unsubscribe > > On Tuesday 21 December 2010 4:16:00 pm William Gordon Rutherdale > (rutherw) > wrote: > > I attempted to unsubscribe from this list (for the holidays) without > > success. > > > > Could anyone please help me. I am continuing to get messages from > the > > list. > > > > I broke open the message header and did as it said for unsubscribing. > > > > See below for what the majordomo sent back. > > > > -Will > > > > >>>> unsub pgsql-general > > > > The unsubscribe command did not succeed. > > > > No e-mail addresses matching > > "William Gordon Rutherdale (rutherw)" > > are subscribed to the pgsql-general mailing list. > > > > > > Valid commands processed: 1 > > 0 succeeded, 0 stalled, and 1 failed. > > > > > > Use the following command: > > sessioninfo ea4ce02c75fbad19425a55ff2bd2bc20b17fd794 > > to see technical information about this session. > > You may want to try the Web link at the bottom of the page and access > your > subscription from there. You will need to know the password you where > issued > when you joined though. > > -- > Adrian Klaver > adrian.kla...@gmail.com Unfortunately I haven't a clue what my password is supposed to be. Clearly something is broken in the system, as it's contradicting itself by sending me emails but failing to recognise that same email address when I attempt to unsubscribe. This is not my fault. I need to have this problem fixed today. This is my last day before the holidays. Could you please just fix it and get me off the list. -Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cannot unsubscribe
I attempted to unsubscribe from this list (for the holidays) without success. Could anyone please help me. I am continuing to get messages from the list. I broke open the message header and did as it said for unsubscribing. See below for what the majordomo sent back. -Will >>>> unsub pgsql-general The unsubscribe command did not succeed. No e-mail addresses matching "William Gordon Rutherdale (rutherw)" are subscribed to the pgsql-general mailing list. Valid commands processed: 1 0 succeeded, 0 stalled, and 1 failed. Use the following command: sessioninfo ea4ce02c75fbad19425a55ff2bd2bc20b17fd794 to see technical information about this session. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extract created and last modified data
I have a CMS with a table of items, and another table serving as a log of operations performed on the items. The revelent table structure is as follows: items itm_id | usr_id_create | itm_date_create | usr_id_modify | itm_date_modify | . itm_id is a serial primary key. usr_id_* are the keys of the users who created and last modified the item. item_date_* are timestamps for creation and last modification times. changelog itm_id | usr_id | log_timestamp | log_op itm_id and usr_id are foreign keys into the respective item and user tables. log_timestamp is when the last change was made and log_op was the operation performed. I realised that the creation and last modified data was being stored in two places, and this is causing some issues with desynching. I'm thinking of removing the creation and last modified fields from the items table and using a view to fetch the create data and ID, and the last modified date and ID from the changelog table instead. However I'm not sure of how to do this, and how it would impact performance. Can anybody help with writing the query to get a view that replicates the items table except with the create and last modified user IDs and timestamps loaded from the changelog table? If there is anybody who has actually implemented something similar, what kind of performance impact would it have? -- 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] Need help understanding vacuum verbose output
> That last message prints tups_vacuumed, but those other ones are counting > all the removed item pointers. So apparently Gordon had a whole lot of > pre-existing DEAD item pointers. I wonder why ... Perhaps this will help. Here's the entire test. Start with a newly loaded table with 5,063,463 rows. 1. delete 467 rows 2. delete 4,220 rows 3. vacuum 4. delete 5,091 rows 5. delete 3,832 rows 6. delete 10,168 rows 7. delete 10,469 rows 8. delete 16,318 rows 9. vacuum (output shown in original post) > 13 There were 4687 unused item pointers. This corresponds to the 1st 2 steps, I presume, where the 4687 unused pointer were created by the 1st vacuum. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-understanding-vacuum-verbose-output-tp2265895p2266912.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Need help understanding vacuum verbose output
Yes, and also from the original post: > 3 INFO: scanned index "authors_archive_pkey" to remove 45878 row > versions > 4 DETAIL: CPU 0.05s/0.34u sec elapsed 0.41 sec. > 5 INFO: "authors_archive": removed 45878 row versions in 396 pages > 6 DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. Line 5 is talking about the relation, not the index, right? Anyway, I know it did remove 45878 row versions, cuz that's how many I deleted, and the stats now say 0 dead tuples. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-understanding-vacuum-verbose-output-tp2265895p2266825.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Need help understanding vacuum verbose output
OK, so if it knew that all vacuumable tuples could be found in 492 pages, and it scanned only those pages, then how could it be that it reports 16558 removable tuples from those 492 pages, when it has already reported earlier that it removed 45878 tuples -- a number we know in fact to be correct? How could both statements be correct? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-understanding-vacuum-verbose-output-tp2265895p2266792.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Need help understanding vacuum verbose output
Hi, Running 8.4.4 on Centos. A couple of these numbers don't make sense to me. (I added line numbers for reference) 1 vacuum verbose authors_archive; 2 INFO: vacuuming "public.authors_archive" 3 INFO: scanned index "authors_archive_pkey" to remove 45878 row versions 4 DETAIL: CPU 0.05s/0.34u sec elapsed 0.41 sec. 5 INFO: "authors_archive": removed 45878 row versions in 396 pages 6 DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. 7 INFO: index "authors_archive_pkey" now contains 5012898 row versions in 13886 pages 8 DETAIL: 45878 index row versions were removed. 9 138 index pages have been deleted, 12 are currently reusable. 10 CPU 0.00s/0.00u sec elapsed 0.00 sec. 11 INFO: "authors_archive": found 16558 removable, 7300 nonremovable row versions in 492 out of 51958 pages 12 DETAIL: 0 dead row versions cannot be removed yet. 13 There were 4687 unused item pointers. 14 0 pages are entirely empty. I was the only one touching this table. Since the previous vacuum, I deleted 45,878 rows. Line 5 says it removed all 45,878 tuples, that's fine. Line 11 now says it found only 16,558 removable tuples. What does this mean? Line 11 also says there are 7,300 non-removable tuples. I expected 0. What does this mean? Line 12 says that 0 dead tuples cannot be removed, which seems correct to me, but seems to contradict Line 11. Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-understanding-vacuum-verbose-output-tp2265895p2265895.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Help writing a query to predict auto analyze
alvherre wrote: > > n_live_tup and n_dead_tup corresponds to the current numbers, > whereas "last analysis tuples" are the values from back when the > previous analyze ran. These counters keep moving per updates, deletes, > inserts, they are not static. > > OK. Do you know how can I get the values from back when the previous analyze ran? -- View this message in context: http://old.nabble.com/Help-writing-a-query-to-predict-auto-analyze-tp28610247p28616817.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Help writing a query to predict auto analyze
alvherre wrote: > > Excerpts from Gordon Shannon's message of mié may 19 11:49:45 -0400 2010: > >> at: last analysis tuples = pg_class.reltuples >> >> I'm the least confident about the last one -- tuples as of last analyze. >> Can anyone confirm or correct these? > > In 8.4 it's number of dead + lives tuples that there were in the previous > analyze. See pgstat_recv_analyze in src/backend/postmaster/pgstat.c. > (In 9.0 it's been reworked a bit.) > > I'm sorry, I'm not following you. Are you saying that "last analysis tuples" is "number of dead + live tuples from the previous anlyze"? If so, that would really confuse me because X would always be 0: X = lt + dt - at X = pg_stat_user_tables.n_live_tup + n_dead_tup - (n_live_tup + n_dead_tup) X = 0 or is there something else wrong with the formula? --gordon -- View this message in context: http://old.nabble.com/Help-writing-a-query-to-predict-auto-analyze-tp28610247p28614875.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Help writing a query to predict auto analyze
In an effort to fine-tune my table storage parameters so tables are analyzed
at the optimal time, I have written a query to show how soon my tables will
be auto-analyzed. But my results to not jive with what I see autovacuum
doing, i.e. there are tables that are millions of rows past the threshold
that haven't been analyzed for 2 weeks, and autovacuum does not want to auto
analyze them, so I conclude that my query must be wrong.
The docs say that tables will be auto analyzed when (paraphrasing) the
number of tuples inserted or updated since the last analyze exceeds the
analyze base threshold plus the product of the analyze scale factor and the
number of tuples.
After a reading of the some of the code in autovacuum.c, it appears the
formula used is something like this. (Note that I'm not quite sure how
"number of tuples inserted or updated since the last analyze" translates in
code as "n_live_tup + n_dead_tup - [tuples at last analyze]", but I'll trust
the code is correct, and I need only understand how to get the values of the
variables.)
T = bt + (sf * rt)
X = lt + dt - at
will analyze when X > T
T: Threshold
X: test value
bt: base threshold
sf: scale factor
rt: rel tuples
lt: live tuples
dt: dead tuples
at: last analysis tuples
The next step was to get these values from the system tables. Here's what I
used:
bt: base threshold = current_setting('autovacuum_analyze_threshold') or
table override setting
sf: scale factor = current_setting('autovacuum_analyze_scale_factor') or
table override setting
rt: rel tuples = pg_class.reltuples
lt: live tuples = pg_stat_user_tables.n_live_tup
dt: dead tuples = pg_stat_user_tables.n_dead_tup
at: last analysis tuples = pg_class.reltuples
I'm the least confident about the last one -- tuples as of last analyze.
Can anyone confirm or correct these?
Version: PostgreSQL 8.4.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
Thanks!
--gordon
--
View this message in context:
http://old.nabble.com/Help-writing-a-query-to-predict-auto-analyze-tp28610247p28610247.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Crazy looking actual row count from explain analyze
Tom Lane-2 wrote: > > My first suspicion > is that those are unvacuumed dead rows ... what's your vacuuming policy > on this database? > Ah, I didn't know that number included dead tuples. That probably explains it. pg_stat_user_tables says the table has 370,269 dead tuples. On this table, I have autovacuum_vacuum_scale_factor set to 0.02, so I believe the table will have to have 869K dead tuples before vacuum will kick in. > I have already fixed this query by adding a better index. Tom Lane-2 wrote: > > I think the new index might have "fixed" things largely by not bothering > to index already-dead rows. > Actually, I put a partial index on status, where != 'V'. That fits our usage pattern of 99% of the records being 'V', so it's a tiny index and satisifies this type of query very quickly. Thanks, --gordon -- View this message in context: http://old.nabble.com/Crazy-looking-actual-row-count-from-explain-analyze-tp28517643p28518862.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Crazy looking actual row count from explain analyze
Running 8.4.3, I have a table with 43 million rows. Two of the columns are
(topic_id int not null) and (status message_status_enum not null), where
message_status_enum is defined as
CREATE TYPE message_status_enum AS ENUM ( 'V', 'X', 'S', 'R', 'U', 'D' );
Among the indexes there is this:
"m_20100201_topic_multi" btree (topic_id, status, source_category_id,
alg_ci_rank_rollup)
..see that topic_id and status are the leading edge of the index.
Fact: there are no rows with status 'S' or 'X'
Fact: there are no rows with topic_id = 1
Consider, then...
explain analyze select count(*) from m_20100201 where status in ('S','X');
QUERY
PLAN
-
Aggregate (cost=987810.75..987810.76 rows=1 width=0) (actual
time=2340.193..2340.194 rows=1 loops=1)
-> Bitmap Heap Scan on m_20100201 (cost=987806.75..987810.75 rows=1
width=0) (actual time=2340.191..2340.191 rows=0 loops=1)
Recheck Cond: (status = ANY ('{S,X}'::message_status_enum[]))
-> Bitmap Index Scan on m_20100201_topic_multi
(cost=0.00..987806.75 rows=1 width=0) (actual time=2334.371..2334.371
rows=126336 loops=1)
Index Cond: (status = ANY ('{S,X}'::message_status_enum[]))
What I don't understand is the "actual rows" of 126,336 in the bitmap index
scan. I would expect it to have to scan every index entry, but doesn't this
output mean that it's *returning* 126K rows from that scan? Whereas I think
it should return zero.
I have already fixed this query by adding a better index. But the point of
this post is simply to understand this explain analyze output. Thanks!
--gordon
--
View this message in context:
http://old.nabble.com/Crazy-looking-actual-row-count-from-explain-analyze-tp28517643p28517643.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Tracking down log segment corruption
Sounds like you're on it. Just wanted to share one additional piece, in case it helps. Just before the ALTER INDEX SET TABLESPACE was issued, there were some writes to the table in question inside a serializable transaction. The transaction committed at 11:11:58 EDT, and consisted of, among a couple thousand writes to sibling tables, 4 writes (unknown combination of inserts and updates) to cts_20100501, which definitely effected the index in question. In any case, I will cease and desist from ALTER SET TABLESPACE for a while!. Thanks! Gordon Between 11:11:56 and 11:11:58 EDT (11 sec before the crash), there were On Sun, May 2, 2010 at 3:16 PM, Tom Lane wrote: > Found it, I think. ATExecSetTableSpace transfers the copied data to the > slave by means of XLOG_HEAP_NEWPAGE WAL records. The replay function > for this (heap_xlog_newpage) is failing to pay any attention to the > forkNum field of the WAL record. This means it will happily write FSM > and visibility-map pages into the main fork of the relation. So if the > index had any such pages on the master, it would immediately become > corrupted on the slave. Now indexes don't have a visibility-map fork, > but they could have FSM pages. And an FSM page would have the right > header information to look like an empty index page. So dropping an > index FSM page into the main fork of the index would produce the > observed symptom. > > I'm not 100% sure that this is what bit you, but it's clearly a bug and > AFAICS it could produce the observed symptoms. > > This is a seriously, seriously nasty data corruption bug. The only bit > of good news is that ALTER SET TABLESPACE seems to be the only operation > that can emit XLOG_HEAP_NEWPAGE records with forkNum different from > MAIN_FORKNUM, so that's the only operation that's at risk. But if you > do do that, not only are standby slaves going to get clobbered, but the > master could get corrupted too if you were unlucky enough to have a > crash and replay from WAL shortly after completing the ALTER. And it's > not only indexes that are at risk --- tables could get clobbered the > same way. > > My crystal ball says there will be update releases in the very near > future. > >regards, tom lane >
Re: [GENERAL] Tracking down log segment corruption
On Sun, May 2, 2010 at 12:52 PM, Tom Lane wrote:
> Gordon Shannon writes:
> > Bingo. Yes it is reasonable. It was 25 seconds between my altering the
> > index in question and the server crash.
>
> Sounds like we have a smoking gun. Could you show all your non-default
> postgresql.conf settings on the master? I'm wondering about
> full_page_writes in particular, but might as well gather all the
> relevant data while we're at it. Easiest way is:
>
> select name,setting from pg_settings where source not in
> ('default','override');
>
>regards, tom lane
>
archive_command | cp %p /var/lib/pgsql/wal/%f.wrk; mv
/var/lib/pgsql/wal/%f.wrk /var/lib/pgsql/wal/%f
archive_mode| on
authentication_timeout | 20
autovacuum_analyze_scale_factor | 0.05
autovacuum_freeze_max_age | 15
autovacuum_vacuum_cost_delay| -1
autovacuum_vacuum_scale_factor | 0.1
checkpoint_segments | 128
checkpoint_warning | 300
commit_siblings | 1
cpu_index_tuple_cost| 0.001
cpu_operator_cost | 0.0005
cpu_tuple_cost | 0.003
DateStyle | ISO, MDY
deadlock_timeout| 5000
default_text_search_config | pg_catalog.english
effective_cache_size| 6291456
lc_messages | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_time | en_US.UTF-8
listen_addresses| *
log_autovacuum_min_duration | 0
log_destination | stderr
log_directory | /var/log/postgres
log_filename| pg%d.log
log_line_prefix | %p %u %r %t [%l]
log_min_duration_statement | 18
log_rotation_age| 1440
log_rotation_size | 0
log_temp_files | 0
log_timezone| US/Eastern
log_truncate_on_rotation| on
logging_collector | on
maintenance_work_mem| 8388608
max_connections | 500
max_stack_depth | 2048
port| 5432
search_path | public, archive, _slony_cluster
shared_buffers | 2359296
standard_conforming_strings | on
synchronous_commit | off
temp_tablespaces|
ts27,ts28,ts29,ts30,ts31,ts32,ts33,ts34,ts35,ts36,ts37
TimeZone| US/Eastern
timezone_abbreviations | Default
track_functions | pl
vacuum_freeze_min_age | 5
vacuum_freeze_table_age | 13
wal_buffers | 1024
work_mem| 204800
Re: [GENERAL] Tracking down log segment corruption
On Sun, May 2, 2010 at 12:10 PM, Tom Lane wrote: > No, this would be a pg_database row with that OID. But it looks like > you found the relevant index anyway. > > Yup, realized that on second reading. > > These commands worked fine on the master, yet this seems suspiciously > > relevant. > > > Yeah, perhaps so. What time did the failure on the standby occur (how > long after you did those moves)? Is it reasonable to assume that this > was the first subsequent access to the index? > > Bingo. Yes it is reasonable. It was 25 seconds between my altering the index in question and the server crash. My local commands (in MDT, plus my machine is 15 sec ahead of the server): 09:10:52> alter index cts_20100501_natural_uk set tablespace ts30; ALTER INDEX Time: 787.790 ms 09:11:41> alter index cts_20100501_pkey set tablespace ts30; ALTER INDEX Time: 468.526 ms 09:11:51> alter index cts_20100501_topic_date_nk set tablespace ts30; ALTER INDEX Time: 385.322 ms 09:11:59> alter index cts_20100501_updated_nk set tablespace ts30; ALTER INDEX Time: 963.150 ms 09:12:10> alter table cts_20100501 set tablespace ts29; ALTER TABLE And from the wsb log (times in EDT): 4158 2010-05-02 11:12:09 EDT [26446]LOG: restored log file "00013C7700C4" from archive 4158 2010-05-02 11:12:09 EDT [26447]WARNING: specified item offset is too large 4158 2010-05-02 11:12:09 EDT [26448]CONTEXT: xlog redo insert: rel 48777166/22362/48778276; tid 2/2 4158 2010-05-02 11:12:09 EDT [26449]PANIC: btree_insert_redo: failed to add item
Re: [GENERAL] Tracking down log segment corruption
On Sun, May 2, 2010 at 11:02 AM, Tom Lane wrote: > > > Hmm ... AFAICS the only way to get that message when the incoming TID's > offsetNumber is only 2 is for the index page to be completely empty > (not zeroes, else PageAddItem's sanity check would have triggered, > but valid and empty). What that smells like is a software bug, like > failing to emit a WAL record in a case where it was necessary. Can you > identify which index this was? (Look for relfilenode 48778276 in the > database with OID 22362.) If so, can you give us any hints about > unusual things that might have been done with that index? > Interesting. There is no pg_class entry for 22362. There is, however, an entry for that filenode. It's an index I created Sat AM, about 6AM. select oid, * from pg_class where relfilenode=48778276; -[ RECORD 1 ]--+--- oid| 48777488 relname| cts_20100501_topic_date_nk relnamespace | 2200 reltype| 0 relowner | 16412 relam | 403 relfilenode| 48778276 reltablespace | 48777166 relpages | 2476 reltuples | 58879 reltoastrelid | 0 reltoastidxid | 0 relhasindex| f relisshared| f relistemp | f relkind| i relnatts | 2 relchecks | 0 relhasoids | f relhaspkey | f relhasrules| f relhastriggers | f relhassubclass | f relfrozenxid | 0 relacl | null reloptions | null Possibly relevant facts: - The WSB server went active on Friday around 3:30PM - On Friday evening, I added about 11 tablespaces. I noted the new files on the WSB, no problems. - On Sat morning, I created a partitioned table cts_20100501 (inherits from another table) and 4 indexes. - This morning, I was doing some table maintenance on the master and discovered I had created this table and its indexes in the wrong tablespace. I wanted the table in ts29, but had it in ts30. Vice versa for the indexes. So I moved them. This is from my command history: alter index cts_20100501_natural_uk set tablespace ts30; alter index cts_20100501_pkey set tablespace ts30; alter index cts_20100501_topic_date_nk set tablespace ts30; alter index cts_20100501_updated_nk set tablespace ts30; alter table cts_20100501 set tablespace ts29; These commands worked fine on the master, yet this seems suspiciously relevant. > > Any suggestions? > > As far as recovering goes, there's probably not much you can do except > resync the standby from scratch. But it would be nice to get to the > bottom of the problem, so that we can fix the bug. Have you got an > archive of this xlog segment and the ones before it, and would you be > willing to let a developer look at them? > > Before I received your reply, I had already started the re-sync, and unfortunately already deleted the wal logs in question. If it happens again, I will certainly keep them, and would be happy to share them. Regards, Gordon
Re: [GENERAL] Tracking down log segment corruption
I just got ran into the same problem. Both servers are running 8.4.3, and the standby server had been running for 2 days, processing many thousands of logs successfully. Here's my error: 4158 2010-05-02 11:12:09 EDT [26445]LOG: restored log file "00013C7700C3" from archive 4158 2010-05-02 11:12:09 EDT [26446]LOG: restored log file "00013C7700C4" from archive 4158 2010-05-02 11:12:09 EDT [26447]WARNING: specified item offset is too large 4158 2010-05-02 11:12:09 EDT [26448]CONTEXT: xlog redo insert: rel 48777166/22362/48778276; tid 2/2 4158 2010-05-02 11:12:09 EDT [26449]PANIC: btree_insert_redo: failed to add item 4158 2010-05-02 11:12:09 EDT [26450]CONTEXT: xlog redo insert: rel 48777166/22362/48778276; tid 2/2 4151 2010-05-02 11:12:09 EDT [1]LOG: startup process (PID 4158) was terminated by signal 6: Aborted 4151 2010-05-02 11:12:09 EDT [2]LOG: terminating any other active server processes Any suggestions? Charles Duffy-5 wrote: > > > [14-1] LOG: restored log file "000100140010" from archive > [15-1] WARNING: specified item offset is too large > [15-2] CONTEXT: xlog redo insert_upper: rel 1663/16384/17763; tid 2960/89 > [16-1] PANIC: btree_insert_redo: failed to add item > [16-2] CONTEXT: xlog redo insert_upper: rel 1663/16384/17763; tid 2960/89 > [1-1] LOG: startup process (PID 17310) was terminated by signal 6: > Aborted > [2-1] LOG: aborting startup due to startup process failure > > -- View this message in context: http://old.nabble.com/Tracking-down-log-segment-corruption-tp21121136p28427922.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] unexplained autovacuum to prevent wraparound
That looks like the fix for this, thanks! I will try to upgrade soon. -- Gordon On Sun, Mar 14, 2010 at 7:43 AM, Alvaro Herrera wrote: > Gordon Shannon escribió: > > Ah, now I see what you meant. Forgive me, I thought you were referring > to > > the pg_autovacuum table in 8.3 where you have to specifiy something for > each > > column, and -1 says use the default. It appears in 8.4.0 I have to > > explicitly set ALL (?) other storage parameters to -1 to get the default, > > otherwise I am getting zero for each value?? I don't believe the > > documentation mentions this rather important detail: > > > http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS > . > > Did I miss it somewhere? > > Err, no, that would be a bug. Please update to 8.4.2, I think we fixed > it there. > > revision 1.28.2.1 > date: 2009-08-27 13:19:31 -0400; author: alvherre; state: Exp; lines: > +10 -10; > Fix handling of autovacuum reloptions. > > In the original coding, setting a single reloption would cause default > values to be used for all the other reloptions. This is a problem > particularly for autovacuum reloptions. > > Itagaki Takahiro > > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
Re: [GENERAL] unexplained autovacuum to prevent wraparound
Ah, now I see what you meant. Forgive me, I thought you were referring to the pg_autovacuum table in 8.3 where you have to specifiy something for each column, and -1 says use the default. It appears in 8.4.0 I have to explicitly set ALL (?) other storage parameters to -1 to get the default, otherwise I am getting zero for each value?? I don't believe the documentation mentions this rather important detail: http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS. Did I miss it somewhere? Thanks! Gordon On Fri, Mar 12, 2010 at 4:45 PM, Gordon Shannon wrote: > This is 8.4, there is no pg_autovacuum table. I set it like this: > > alter table foo set (autovacuum_analyze_scale_factor=0.01); > > > > > On Fri, Mar 12, 2010 at 4:31 PM, Alvaro Herrera < > alvhe...@commandprompt.com> wrote: > >> Gordon Shannon escribió: >> >> > One possibly interesting thing is that this seems to have started just >> after >> > I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more >> > frequent analyze runs. I wonder if that could be related. >> >> You probably set the other values to 0, which includes the freeze age. >> You need to set it (and all other values) to -1 instead. >> >> -- >> Alvaro Herrera >> http://www.CommandPrompt.com/ >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support >> > > > -- If I had more time, I could have written you a shorter letter. (Blaise Pascal)
Re: [GENERAL] unexplained autovacuum to prevent wraparound
Thanks, but I do want 1%. On Fri, Mar 12, 2010 at 5:19 PM, Joshua D. Drake wrote: > On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote: > > This is 8.4, there is no pg_autovacuum table. I set it like this: > > > > alter table foo set (autovacuum_analyze_scale_factor=0.01); > > That is 1% changes. I think you want .10 > > Sincerely, > > Joshua D. Drake > > > > > > > -- > PostgreSQL.org Major Contributor > Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 > Consulting, Training, Support, Custom Development, Engineering > Respect is earned, not gained through arbitrary and repetitive use or Mr. > or Sir. > > -- If I had more time, I could have written you a shorter letter. (Blaise Pascal)
Re: [GENERAL] unexplained autovacuum to prevent wraparound
This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); On Fri, Mar 12, 2010 at 4:31 PM, Alvaro Herrera wrote: > Gordon Shannon escribió: > > > One possibly interesting thing is that this seems to have started just > after > > I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more > > frequent analyze runs. I wonder if that could be related. > > You probably set the other values to 0, which includes the freeze age. > You need to set it (and all other values) to -1 instead. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
[GENERAL] unexplained autovacuum to prevent wraparound
It appears to me that in my 8.4.0 system, autovacuum is running to prevent wraparound contrary to the documentation. I have it set to a tables' relfrozenxid has to get to 1.5 billion before that kicks in: > show autovacuum_freeze_max_age; 15 > show vacuum_freeze_table_age; 13 > show vacuum_freeze_min_age; 10 Table foo has an age just over 1 billion, still well under the freeze_table_age: > select age(relfrozenxid) from pg_class where relname='foo'; age 1055823634 yet, I see this in pg_stat_activity: autovacuum: VACUUM public.foo (to prevent wraparound) One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. Any ideas? These wraparound autovacuums are taking up my vacuum workers so no analyze workers can run. Thanks! Gordon -- View this message in context: http://old.nabble.com/unexplained-autovacuum-to-prevent-wraparound-tp27883825p27883825.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Tsearch2 with Japanese
Ah, I finally found it http://pgfoundry.org/projects/textsearch-ja/ - Original Message - From: "Gordon Callan" To: pgsql-general@postgresql.org Sent: Monday, November 9, 2009 2:36:18 PM GMT -08:00 US/Canada Pacific Subject: [GENERAL] Tsearch2 with Japanese Does anyone know where I can locate a Japanese parser and dictionary to use with Tsearch2? There was a link (http://www.oss.ecl.ntt.co.jp/tsearch2j/ ) to a Contrib at one time but this link is now dead :-( Any leads would be appreciated.
[GENERAL] Tsearch2 with Japanese
Does anyone know where I can locate a Japanese parser and dictionary to use with Tsearch2? There was a link (http://www.oss.ecl.ntt.co.jp/tsearch2j/ ) to a Contrib at one time but this link is now dead :-( Any leads would be appreciated.
[GENERAL] Got could not truncate directory "pg_multixact/offsets": apparent wraparound
Hello, running 8.4 on Centos. Been running production for 6 months. Never saw this message until tonight: LOG: could not truncate directory "pg_multixact/offsets": apparent wraparound In case it helps... Output of pg_controldata: Latest checkpoint's NextMultiXactId: 145725622 Latest checkpoint's NextMultiOffset: 394849408 Contents of pg_multixact/offsets: 08B0 Contents of pg_multixact/members: 178B In conf file: vacuum_freeze_min_age = 10 # (1 billion) autovacuum_freeze_max_age = 15 # (1.5 billion) Oldest txn in cluster (pg_database.datfrozenxid) : 648 Should I be concerned? Thanks Gordon -- View this message in context: http://www.nabble.com/Got-could-not-truncate-directory-%22pg_multixact-offsets%22%3A-apparent-wraparound-tp25287801p25287801.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Getting "insufficient data left in message" on copy with binary
Tom Lane-2 wrote: > > Gordon Shannon writes: > >> ERROR: insufficient data left in message >> CONTEXT: COPY mytable, line 1, column provider_id > >> Anybody seen this? > > No. Can you extract a self-contained test case? > Got it. The problem was a combination of 2 mis-matched data types. Consider this test case: begin--- drop table if exists bar; drop table if exists foo; create table foo ( system_id smallint, credibility real not null ); insert into foo ( system_id, credibility) values (1,1); copy foo to '/tmp/repeat.dat' with binary; create table bar ( system_id int, credibility numeric(10,9) not null ); copy bar from '/tmp/repeat.dat' with binary; copy bar from '/var/lib/pgsql/backups/repeat.dat' with binary; psql:repeat:19: ERROR: insufficient data left in message CONTEXT: COPY bar, line 1, column system_id --end- It's interesting to note that I get this error only when there are 2 bad fields. If I fix only the numeric field, I get "incorrect binary data format" on the int field. If I fix only the smallint field, I get "invalid length in external "numeric" value on the real field. So, my fault, and the fix is obvious. But it does seem like a less than ideal error message. Also, maybe a sentence like this would be helpful on the COPY page: "Be careful that the data types match from 'copy to' to 'copy from'. There is no implicit conversion done in binary mode..." or some such? Cheers -- View this message in context: http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25287583.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Getting "insufficient data left in message" on copy with binary
Hello, I'm running 8.4 on Linux/Centos. I am doing a "copy (select ) to '/absolute/path/to/file.dat' with binary". That works fine. But when I load that file into a table... copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id, content_type_id, provider_id, is_duplicate, is_spam, language_code, profanity, tonality, sentiment, created_time, updated_at) from '/absolute/path/to/file.dat' with binary; ERROR: insufficient data left in message CONTEXT: COPY mytable, line 1, column provider_id The same data works fine without the "with binary". Also, the column it's complaining about, provider_id, is a NOT NULL column, and the data is definitely there -- i.e. not a NULL in data file. I have searched for this message and mostly I see issues related to JDBC drivers, so that doesn't appear relevant. And they all talk about nul bytes (0x00), but again, how can that be relevant when I'm in binary mode? Seems like it should understand null bytes here, if that's what this is about. Anybody seen this? Thanks, Gordon -- View this message in context: http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25282935.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PL/SQL & unset custom variable
Hmmm.
If I do:
select * from pg_settings where name='custom_variable_classes';
I see my entry "phone", but I can't see how I can tell if I've set "phone.id"
GTG
From: Tom Lane [...@sss.pgh.pa.us]
Sent: 02 September 2009 19:16
To: Gordon Ross
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/SQL & unset custom variable
Gordon Ross writes:
> Sorry, no joy :-(
> grails=> SELECT current_setting('phone.id');
> ERROR: unrecognized configuration parameter "phone.id"
> grails=> SELECT coalesce(current_setting('phone.id'),'SYSTEM');
> ERROR: unrecognized configuration parameter "phone.id"
You could use a SELECT from pg_settings to probe for the value
of the parameter without getting an error.
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] PL/SQL & unset custom variable
On 02/09/2009 15:39, "Andreas Kretschmer" wrote:
> Gordon Ross wrote:
>> Is there a way to either test if the custom variable is set, or to specify a
>> global default for the custom variable ?
>
> I think, you can use COALESCE(your_variable, default_value) to solve
> that problem. Try it, it is untested.
Sorry, no joy :-(
grails=> SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter "phone.id"
grails=> SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter "phone.id"
GTG
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/SQL & unset custom variable
(I'm using Postgres 8.3) I have a trigger that references a custom variable. Most of the time this custom variable is set, and I have no problems. However, in certain corner cases the custom variable is not set and the trigger fails. Is there a way to either test if the custom variable is set, or to specify a global default for the custom variable ? Thanks, GTG -- 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] "Could not open relation XXX: No such file or directory"
Yaroslav Tykhiy wrote: By the way, `chkdsk' in Windows or `fsck' in Unix can, in a way, be a _source_ of file loss if the file metadata got damaged badly, e.g., by a system crash, and the file node has to be cleared. So I've always been curious if there is a way to retrieve surviving records from a PostgreSQL database damaged by file loss. Do you know any? (Of course, the only true solution is to have been making backups beforehand, but...) The Ubuntu Linux site has this page on data recovery (also applicable to other Linux flavors): https://help.ubuntu.com/community/DataRecovery I assume that a database file, because of its structure, is harder to recover after it becomes corrupt than, say, an XML file. But any port in a storm, right? Thanks! Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to capture an interactive psql session in a log file?
That does the trick, awesome! I do think it would be great if psql had a "stderr" capture in addition to stdout. Thanks hubert depesz lubaczewski-2 wrote: > > On Thu, Apr 02, 2009 at 10:55:10PM -0700, Gordon Shannon wrote: >> Has anyone solved this issue before? > > have you seen program "script"? > > usage: > > just run script > you will get shell. now run your command you want to capture everything > from - it will work as usual. > after you finish - exit the "script-shell", and check the generated > typescript file > > 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 > > -- View this message in context: http://www.nabble.com/How-to-capture-an-interactive-psql-session-in-a-log-file--tp22862412p22862519.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to capture an interactive psql session in a log file?
What I'm trying to do doesn't seem like it should be that difficult or unusual, but I can't seem to find the right combination of commands to make it happen. I want to have a log file that captures everything from an interactive psql session. Running 8.3.7 with bash shell on Linux. If I use \o or \o |tee http://www.nabble.com/How-to-capture-an-interactive-psql-session-in-a-log-file--tp22862412p22862412.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] TSearch queries with multiple languages
Greetings,
I'm implementing full text search at our company, using Tsearch2 and have read
Chapter 12 (FTS) numerous times and am still unclear about something.
All our data is stored in Postgresql in Unicode.
The data to be searched can be in a number of different languages.
I plan to create a ts_vector column for each corresponding data column to be
searched and use an additional column for the
regconfig where each row will have a configuration name depending on the
language of that row of data.
So, for example, we have a table called node with columns node_id, body;
We add 2 columns, ts_body and ts_config to contain the ts_vector data of body
and the configuration (langugage) in the
other.
ALTER TABLE node ADD column ts_body tsvector, ts_config regconfig;
At install time, the ts_config column will be populated so that it contains the
language/config for each row. We will also
provide a means to keep the ts_body column updated each time the underlying
body data changes.
We then generate the ts_vector column using this configuration:
UPDATE node SET ts_body = to_tsvector(ts_config, body);
Presumably, this will generate tsvector data for every row, using what's in the
regconfig column to determine the language.
Next we create an index on the ts_vector column:
CREATE INDEX node_ts_body on node USING gin(ts_body);
>From the documentation, it seems this index will know what config each row
>has.
OK, now here's where the documentation is sketchy.
When searching, we will generate SQL like this:
SELECT *
FROM node
WHERE (ts_body @@ to_tsquery('english','foo & bar'));
Assuming we have 3 different configurations (all contained in various rows and
defined in the regconfig column), what language(s) will be returned in the
result set? All 3 languages? Is it based on the default_text_search_config ?
Thanks for your help,
Gordon
_
Windows Live™: Keep your life in sync.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
[GENERAL] Getting rows in a very specific order
I'm considering using an array of ints column in a table which lists a
row's ancestry. For example, if item 97 is contained within itme 68
and that item is contained with in 31 and that item is contained
within item 1 then the value of the hierachy column would be
{1,31,68,97}, the numbers refer to the primary keys of the rows.
If I use the value of the hierarchy column in a query I can get all
the rows that a given row is an descendant of. (SELECT * FROM items
WHERE itm_id IN (1,31,68,97), for example. However, I need the rows
to be in the correct order, ie the root node first, child second,
grandchild third etc. I'm guessing the order can be set with an ORDER
BY, but I've only ever used ORDER BY to order by ascending or
descending order of a single column. Is there a way to retrieve the
rows explicitly in the order they are listed in the hierarchy array?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updates and deletes with joins
I'm working on a CMS, one of the features of the CMS in question is that only one user can edit an item at any given time. I've implemented this by having one table that holds the items, and another table that holds locks. A lock row consists of the ID of the item locked, a timestamp indicating when the item was locked, an interval indicating when the locks expires and a string that holds a reason for the item currently being locked. I want to be able to restrict any query that updates or deletes from the articles table so that they can only occur if there isn't a corresponding entry in the locks table. As far as I can tell, however, you can't join tables when doing updates or deletes. I know on the PHP side I can attempt to do a select on the locks table and only perform the delete if the select returns 0 rows, but I'd rather the update or delete query itself does the checking. Can anyone help out? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Results of stored procedures in WHERE clause
I have a table representing tree structures of pages on a website. they have an itm_id column (integer key) and an itm_parent column (pointer to item's parent node). Any item with an itm_parent of 0 is a root node, representing a website. Anything with a non-zero parent is a non-root node representing a folder or document in a website. I need to be able to do queries that restrict my result set to items belonging to a specified site and ignore all nodes that belong to different sites. To determine the ID of the site an item belongs to I wrote a stored procedure: CREATE OR REPLACE FUNCTION cms.getroot(node integer) RETURNS integer AS $BODY$DECLARE thisnodeinteger := node; thisparent integer := node; BEGIN WHILE thisparent != 0 LOOP SELECT itm_id, itm_parent INTO thisnode, thisparent FROM cms.cms_items WHERE itm_id = thisparent; END LOOP; RETURN thisnode; END; $BODY$ LANGUAGE 'plpgsql' STABLE COST 100; This returns the ID of the root node for non-root nodes, the node's own ID for root-nodes and NULL for invalid IDs. I'm writing a query to do document searching (the version given is simplified to the problem in hand). SELECT cms_v_items.* , getroot (cms_v_items.itm_id) AS itm_root FROM cms_v_items WHERE itm_root = ?; I was hoping this query would return a set of items that had the same root node. Instead it throws an error, column itm_root does not exist. I'm obviously doing something wrong here, but what? -- 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] Results of stored procedures in WHERE clause
On May 20, 5:02 pm, Gordon <[EMAIL PROTECTED]> wrote: > I have a table representing tree structures of pages on a website. > they have an itm_id column (integer key) and an itm_parent column > (pointer to item's parent node). Any item with an itm_parent of 0 is > a root node, representing a website. Anything with a non-zero parent > is a non-root node representing a folder or document in a website. > > I need to be able to do queries that restrict my result set to items > belonging to a specified site and ignore all nodes that belong to > different sites. To determine the ID of the site an item belongs to I > wrote a stored procedure: > > CREATE OR REPLACE FUNCTION cms.getroot(node integer) > RETURNS integer AS > $BODY$DECLARE > thisnodeinteger := node; > thisparent integer := node; > BEGIN > WHILE thisparent != 0 LOOP > SELECT itm_id, itm_parent > INTO thisnode, thisparent > FROM cms.cms_items > WHERE itm_id = thisparent; > END LOOP; > RETURN thisnode; > END; > $BODY$ > LANGUAGE 'plpgsql' STABLE > COST 100; > > This returns the ID of the root node for non-root nodes, the node's > own ID for root-nodes and NULL for invalid IDs. > > I'm writing a query to do document searching (the version given is > simplified to the problem in hand). > > SELECT cms_v_items.* , > getroot (cms_v_items.itm_id) AS itm_root > FROM cms_v_items > WHERE itm_root = ?; > > I was hoping this query would return a set of items that had the same > root node. Instead it throws an error, column itm_root does not > exist. > > I'm obviously doing something wrong here, but what? Is what I'm trying to do even possible? I'm really struggling to find much help with Google on this topic. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining weather a query fired a trigger
I'm adding full text search to a CMS project after we upgraded to 8.3 of Postgres. I'd like to do a bit of testing before deploying it. I added columns to the pertinent tables for storing tsvectors, and was looking at my query code to update it so these columns get updated where appropriate when I discovered triggers as a solution to let this all happen in a way that's completely invisible and will require no rewriting of the existing queries. I set up triggers to fire on insert or update to do the job and it all seems to work fine. But I do have one concern regarding performance. The tsvector only needs to be updated if the title, summary, or keywords fields have changed. If they are the same after an update then there is no need to run them. Doing so would only cause a new tsvector to be generated when it wasn't necessary. So what I want to know is, is there a way to tell if executing a query caused a trigger to fire? I don't need anything fancy like notify and listen, I just want to see what the database is doing for testing purposes. For example by looking at the logs and seeing what activity was caused by a given query. Does this stuff get logged? -- 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] Cast character to boolean
On Mar 19, 5:52 pm, [EMAIL PROTECTED] wrote: > On Mar 18, 9:18 am, Gordon <[EMAIL PROTECTED]> wrote: > > > > > I'm currently refactoring a database that somebody else designed. > > When the database was designed he used character columns with a length > > of 1 char to represent some values that really should have been > > represented as booleans. He used 'y' for true and 'n' for false. > > > I want to cast these columns into the correct type, because you could > > in theory set the columns in question to any single character value. > > I don't seem to be able to do so, however, the database keeps claiming > > that the cast cannot be done. > > > I tried casting the columns in question to character varying and then > > changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This > > wasn't a problem. But casting from this format to boolean still gives > > an error. > > > Does anybody know how to do this? > > Have yout tried > ALTER TABLE foo ALTER col TYPE boolean USING CASE WHEN col = 'y' THEN > true WHEN column = 'n' then FALSE END; I did find a solution in the end but it was nothing like as elegant as yours. In the end I created two new boolean columns, updated the values in the new columns depending on the values in the old columns, dropped the old columns and renamed the new boolean columns to the names of the deleted columns. As you cn imagine, not a fun procedure. I'll keep this post bookmarked though if I ever have to do anything like that again. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cast character to boolean
I'm currently refactoring a database that somebody else designed. When the database was designed he used character columns with a length of 1 char to represent some values that really should have been represented as booleans. He used 'y' for true and 'n' for false. I want to cast these columns into the correct type, because you could in theory set the columns in question to any single character value. I don't seem to be able to do so, however, the database keeps claiming that the cast cannot be done. I tried casting the columns in question to character varying and then changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This wasn't a problem. But casting from this format to boolean still gives an error. Does anybody know how to do this? -- 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] Query meltdown: caching results
(Sorry for the repost but I thought this would be appropriate to both
groups. I did tell Google to delete my first post but odds are some
guys got that copy already anyway)
After a lot of hairpulling, I finally found a mechanism in PHP for
doing what I wanted. I just had to know 2 things:
1) How to get the PDO engine to use my customized prepared statement
class instead of PDOStatement
2) Extending PDOStatement to transparently add results caching is too
difficult and complex in the timeframe required
Once I knew these things I made a PDOStatement extension class that
instead of trying to transparently add caching to the existing methods
added a couple of new ones instead. Code below:
resultCache [$hash])
{
$this -> misses++;
// Execute the query
if ($this -> execute ($params))
{
// Cache the results
$this -> resultCache [$hash] = $this -
> fetchAll
(PDO::FETCH_ASSOC);
}
}
else
{
$this -> hits++;
}
return ($this -> resultCache [$hash]);
}
public function tell (array $params = array ())
// Execute a prepared statement that causes the database to be
modified
{
// Execute the query
if ($this -> execute ($params))
{
$rowCount = $this -> rowCount ();
if ($rowCount)
{
// Tell the parent Database object to
clear statement caches
$this -> database -> clearResults ();
}
return ($rowCount);
}
}
public function clearResults ()
// Clear cache
{
$this -> resultCache = array ();
}
private function __construct ($db)
// Class constructor
{
$this -> database= $db;
//print_r ($this);
}
}
class Database extends PDO
// Adds some extra functionality to the built in PDO class
{
private $statementCache = array ();
private $txCount= 0;
private $txErr = false;
// Prepared statement cacheing
public function prepare ($statement, array $options = array
())
{
$hash = md5 ($statement);
if ((!isset ($this -> statementCache [$hash]))
|| (!is_object ($this -> statementCache [$hash])))
{
//echo ('Preparing statement "'.
$statement .'"');
$this -> statementCache [$hash] =
parent::prepare ($statement,
$options);
}
else
{
//echo ('Statement "' . $statement . '"
already prepared');
}
return ($this -> statementCache [$hash]);
}
public function clearResults ()
// Clear the results cache of all associated prepared
statements
{
foreach ($this -> statementCache as $thisStatement)
{
$thisStatement -> clearResults ();
}
}
// Add support for transaction nesting
public function beginTransaction ()
{
if (($this -> txCount == 0) && (!$this -> txErr))
{
$result = parent::beginTransaction ();
}
$this -> txCount ++;
if (DEBUG_TX)
{
echo ('begin: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
public function commit ()
{
$this -> txCount --;
if ($this -> txCount <= 0)
{
$this -> txErr? $result =
parent::rollback (): $result =
parent::commit ();
$this -> txErr = false;
}
if (DEBUG_TX)
{
echo ('commit: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
public function rollback ()
{
$this -> txErr = true;
$this -> txCount --;
if ($this -> txCount <= 0)
{
$result = parent::rollback ();
$this -> txErr = false;
}
if (DEBUG_TX)
{
echo ('rollback: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
// Housekeeping
pri
Re: [GENERAL] Query meltdown: caching results
On Feb 27, 10:37 am, Gordon <[EMAIL PROTECTED]> wrote:
> On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
>
>
>
> > "Norman Peelman" <[EMAIL PROTECTED]> writes:
> > >> My options are, as far as I can tell,
>
> > >> 1) replace the Database PDO extending class with something else that
> > >> provides query results caching in PHP, or
> > >> 2) get Postgres itself to cache the results of queries to avoid
> > >> running them repeatedly during a session.
>
> > You might consider looking at memcached. One way to use it would be to have
> > the PHP application check for the cached object first and use it rather than
> > do any database queries. Then you can use pgmemcached to allow triggers to
> > invalidate cached objects whenever the underlying data changes. (Or you
> > could
> > even just use pl/php to update or invalidate the cached object through the
> > same code library)
>
> > --
> > Gregory Stark
> > EnterpriseDB http://www.enterprisedb.com
> > Ask me about EnterpriseDB's Slony Replication support!
>
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
>
> Thanks for the replies, but the problem is really centered around how
> my script interacts with the database. I know what the problem is,
> and if I was using a structured approach I could figure out
> workarounds fairly quickly, but I chose to do this project in OOP for
> a few reasons (the old CMS was hacked together in a hurry by an
> employee who is now long gone, is horribly written and completely
> unmaintainable, the size of the project seemed to warrant an OOP
> approach, we recently upgraded to PHP 5 and I wanted to take advantage
> of the new features, PDO and prepared statements appeared from the
> documentation to offer major security and performance enhancements,
> and I wanted to increase my experience with OOP development as most
> work I've done before now has been structured) and the high level of
> compartmentalization that OOP demands is coming back to haunt me
> now.
>
> The problem comes when a document is published or unpublished. All
> the documents that relate to that document need to be republished to
> add or remove the link to the document in question. When the document
> is published or unpublished the script gets related documents (at the
> moment other documents that share the same folder, but this may change
> in the future to cover more related content) and republishes them.
>
> Each document has associated data, such as the ID of the template to
> apply, its parent, its path back to root, etc, that are generated by
> querying the database in various ways. For example, the route back to
> path is fetched by iterativly getting the parent folder, getting that
> folder's parent, etc until the script hits the route. Templates are
> fetched by looking ath the template ID associated with the document.
> If this is 0, then the script goes to the parent folder and gets the
> template associated with the folder. If that is 0 as well then it
> iterativly goes up until it finds a template to apply or until it hits
> the root, in which case it applies a default template. The code
> fragment from the script that does this looks like this:
>
> $db -> beginTransaction ();
> if ($doc= CmsItem::factory ('CmsDoc', intval ($_GET
> ['itm_id']),
> $db, $user))
> {
> if ((($doc -> itemProps ['itm_publish']) && ($doc ->
> unpublish ()))
> || ($doc-> publish ()))
> {
> // Republish related documents
> foreach ($doc -> getSiblingObjects () as $thisDoc)
> {
> if ((get_class ($thisDoc) == 'CmsDoc')
> && ($thisDoc-> itemProps ['itm_publish']))
> {
> $thisDoc-> republish ();
> }
> }
> // Item status changed
> $db -> commit ();
> $_SESSION ['messageStack'][]= ($doc -> itemProps
> ['itm_publish']?
> 'Item published':
> 'Item unpublished');
> }
> else
> {
> // Couldn't change published status
> $db -> rollback ();
> $_SESSION ['messageStack'][]= ($doc -> item
Re: [GENERAL] Query meltdown: caching results
On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
> "Norman Peelman" <[EMAIL PROTECTED]> writes:
> >> My options are, as far as I can tell,
>
> >> 1) replace the Database PDO extending class with something else that
> >> provides query results caching in PHP, or
> >> 2) get Postgres itself to cache the results of queries to avoid
> >> running them repeatedly during a session.
>
> You might consider looking at memcached. One way to use it would be to have
> the PHP application check for the cached object first and use it rather than
> do any database queries. Then you can use pgmemcached to allow triggers to
> invalidate cached objects whenever the underlying data changes. (Or you could
> even just use pl/php to update or invalidate the cached object through the
> same code library)
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's Slony Replication support!
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
Thanks for the replies, but the problem is really centered around how
my script interacts with the database. I know what the problem is,
and if I was using a structured approach I could figure out
workarounds fairly quickly, but I chose to do this project in OOP for
a few reasons (the old CMS was hacked together in a hurry by an
employee who is now long gone, is horribly written and completely
unmaintainable, the size of the project seemed to warrant an OOP
approach, we recently upgraded to PHP 5 and I wanted to take advantage
of the new features, PDO and prepared statements appeared from the
documentation to offer major security and performance enhancements,
and I wanted to increase my experience with OOP development as most
work I've done before now has been structured) and the high level of
compartmentalization that OOP demands is coming back to haunt me
now.
The problem comes when a document is published or unpublished. All
the documents that relate to that document need to be republished to
add or remove the link to the document in question. When the document
is published or unpublished the script gets related documents (at the
moment other documents that share the same folder, but this may change
in the future to cover more related content) and republishes them.
Each document has associated data, such as the ID of the template to
apply, its parent, its path back to root, etc, that are generated by
querying the database in various ways. For example, the route back to
path is fetched by iterativly getting the parent folder, getting that
folder's parent, etc until the script hits the route. Templates are
fetched by looking ath the template ID associated with the document.
If this is 0, then the script goes to the parent folder and gets the
template associated with the folder. If that is 0 as well then it
iterativly goes up until it finds a template to apply or until it hits
the root, in which case it applies a default template. The code
fragment from the script that does this looks like this:
$db -> beginTransaction ();
if ($doc= CmsItem::factory ('CmsDoc', intval ($_GET ['itm_id']),
$db, $user))
{
if ((($doc -> itemProps ['itm_publish']) && ($doc -> unpublish
()))
|| ($doc-> publish ()))
{
// Republish related documents
foreach ($doc -> getSiblingObjects () as $thisDoc)
{
if ((get_class ($thisDoc) == 'CmsDoc')
&& ($thisDoc-> itemProps ['itm_publish']))
{
$thisDoc-> republish ();
}
}
// Item status changed
$db -> commit ();
$_SESSION ['messageStack'][]= ($doc -> itemProps
['itm_publish']?
'Item published':
'Item unpublished');
}
else
{
// Couldn't change published status
$db -> rollback ();
$_SESSION ['messageStack'][]= ($doc -> itemProps
['itm_publish']?
'Unable to unpublish item':
'Unable to publish item');
}
}
GetSiblingObjects () runs a query that gets a list of IDs that share
the same parent as the current document. It then iterates the list
and spawns a new CMS item for each item in the list and returns them
as an array. As folders could be returned as well as documents we
only run republish () on those items.
CmsDoc -> publish () and CmsDoc -> unpublish () toggle a boolean
column in the database between true and false for the item being (un)
published. unpublish () also deletes the concrete file associated
with the DB entry.
publish () and republish () write out a concrete HTML file based on
the content stored in the table for the document
Re: [GENERAL] Query meltdown: caching results
On Feb 26, 11:11 am, Gordon <[EMAIL PROTECTED]> wrote: > I'm working on a CMS that, in addition to the database-stored version > of articles for easy searching, sorting, etc, also stores a HTML file > version so pages can be fetched with the minimum of overhead (browsing > articles has no more overhead than accessing any other HTML file on > the server). > > As I've been trying to keep the system modular I've taken to using an > OO approach to the server side scripts, which are all written in PHP 5 > and use PDO for database access. I've also been using prepared > sequences almost exclusively for security and performance reasons. > I've tried to wrap sequences of queries in transactions as well, to > eliminate the "every query is its own transaction" overhead. > > With previous projects which I wrote using structured programming > methods it was quite easy to hold caches of results and keep database > queries to a minimum, but I've found this extremely difficult to pull > off when using the OO approach, and now it's starting to have some > real performance consequences. The biggest one comes when publishing > a document that has siblings. CMS content is organized in a tree with > folders, subfolders and documents. A document can be published, where > both a HTML and database copy exist, or unpublished, where only the > database version exists, thus denying visitors to the site access to > it. Documents in a folder get a sidebar with links to the other > documents in the same folder, and when you change the published status > of a document then all the other documents that are also published in > that folder have to be republished in order to update their > sidebars. > > This means fetching a list of all the documents with the same parent > and that have a published flag status of true, using the text stored > in the database to generate the HTML page and saving it to disk. > Documents have an associated template, which also has to be fetched > from the database. And all documents have data such as their path, > which is a chain of the document's parents back to the root so that > things like breadcrumbs can be generated. > > In the structured approach I'd have just cached stuff like the trail > back to the root as I know it'll be the same for all documents, so I'd > only have to run the sequences of queries to get the full trail once. > But as each instance of a document is independent of all the others > doing things like this is proving really difficult. > > I need to find a way of not running queries that I don't need to, > either in the PHP script, or in the Postgres database. What I need is > for a result set to be cached somewhere, either by Postgres or PHP, so > when it sees the same query again in a given session it just returns > the previously fetched result set. The cache also needs to be able to > disregard its cached result sets when an event that changes a table > occurs (insert, update, delete, etc). > > On the PHP side I've written a simple Database class that extends PDO > and that I use in its place. It's a simple class that basically I use > to allow me to nest calls to beginTransaction(), commit () and > rollback () (It only starts an actual transaction of a counter is 0. > Otherwide it just increments the counter. Commit only actually > commits when the counter is 1, and decrements it otherwise. Rollback > sets an error flag and decrements the counter, and only rolls back > when the counter is 1. If the error flag is set then commit will > actually roll back instead. ) > > My options are, as far as I can tell, > > 1) replace the Database PDO extending class with something else that > provides query results caching in PHP, or > 2) get Postgres itself to cache the results of queries to avoid > running them repeatedly during a session. > > I seem to remember MySQL providing some kind of results caching, can > Postgres do the same?Has anyone else run into similar problems and > how did they overcome them? I have an idea for how to do it but I'm not quite sure how to accomplish it fully. Aspects involving modifications to the tables are going to be particularly problematic. My idea is to extend the PDOStatement class with an internal result cache. I'm already caching PDOStatements in order to prevent the script from trying to prepare the same queries over and over again. The cache will be an array. The execute(), fetch(). fetchall() etc methods will be aware of the array and return values from it if possible. Things risk getting really tricky really quickly, however. If a modification is made to a table, then any or all of the cached data in all the PDOStatements ma
[GENERAL] Query meltdown: caching results
I'm working on a CMS that, in addition to the database-stored version of articles for easy searching, sorting, etc, also stores a HTML file version so pages can be fetched with the minimum of overhead (browsing articles has no more overhead than accessing any other HTML file on the server). As I've been trying to keep the system modular I've taken to using an OO approach to the server side scripts, which are all written in PHP 5 and use PDO for database access. I've also been using prepared sequences almost exclusively for security and performance reasons. I've tried to wrap sequences of queries in transactions as well, to eliminate the "every query is its own transaction" overhead. With previous projects which I wrote using structured programming methods it was quite easy to hold caches of results and keep database queries to a minimum, but I've found this extremely difficult to pull off when using the OO approach, and now it's starting to have some real performance consequences. The biggest one comes when publishing a document that has siblings. CMS content is organized in a tree with folders, subfolders and documents. A document can be published, where both a HTML and database copy exist, or unpublished, where only the database version exists, thus denying visitors to the site access to it. Documents in a folder get a sidebar with links to the other documents in the same folder, and when you change the published status of a document then all the other documents that are also published in that folder have to be republished in order to update their sidebars. This means fetching a list of all the documents with the same parent and that have a published flag status of true, using the text stored in the database to generate the HTML page and saving it to disk. Documents have an associated template, which also has to be fetched from the database. And all documents have data such as their path, which is a chain of the document's parents back to the root so that things like breadcrumbs can be generated. In the structured approach I'd have just cached stuff like the trail back to the root as I know it'll be the same for all documents, so I'd only have to run the sequences of queries to get the full trail once. But as each instance of a document is independent of all the others doing things like this is proving really difficult. I need to find a way of not running queries that I don't need to, either in the PHP script, or in the Postgres database. What I need is for a result set to be cached somewhere, either by Postgres or PHP, so when it sees the same query again in a given session it just returns the previously fetched result set. The cache also needs to be able to disregard its cached result sets when an event that changes a table occurs (insert, update, delete, etc). On the PHP side I've written a simple Database class that extends PDO and that I use in its place. It's a simple class that basically I use to allow me to nest calls to beginTransaction(), commit () and rollback () (It only starts an actual transaction of a counter is 0. Otherwide it just increments the counter. Commit only actually commits when the counter is 1, and decrements it otherwise. Rollback sets an error flag and decrements the counter, and only rolls back when the counter is 1. If the error flag is set then commit will actually roll back instead. ) My options are, as far as I can tell, 1) replace the Database PDO extending class with something else that provides query results caching in PHP, or 2) get Postgres itself to cache the results of queries to avoid running them repeatedly during a session. I seem to remember MySQL providing some kind of results caching, can Postgres do the same?Has anyone else run into similar problems and how did they overcome them? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to make update rapidly?
On Feb 20, 4:03 am, [EMAIL PROTECTED] (hewei) wrote: > table: > CREATE TABLE price ( > TIMESTAMP Timestamp NULL, > idnumeric(5,0) NOT NULL, > price numeric(10,3) NULL, > primary key (id) > ); > sql: > update price set price=* where id=*; > > On Feb 20, 2008 11:56 AM, Webb Sprague <[EMAIL PROTECTED]> wrote: > > > Post the table, the query, and the explain output, and then we can help > > you. > > > On Feb 19, 2008 7:38 PM, hewei <[EMAIL PROTECTED]> wrote: > > > Hi,Every body; > > >I have a table contains 100,000 rows, and has a primary key(int). > > > Now ,I need to execute sql command like "update .. where > > id=*"(id > > > is primary key). > > > I expect execute 1200-1600 sqlcommands per second(1200-1600/s). > > >In test,when the id increase by degrees in sqlcommands, then I can > > reach > > > the speed(1600/s); > > > But in fact , the id in sqlcommands is out of rule, then the speed > > is > > > very slow, just 100/s. > > > what can i do? can you help me ? You really should only use integer/serial for a primary key or bigint/ bigserial if you have a huge amount of records. From the manual on numeric data types: The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section. Numerics are (AFAIK) actually stored as strings, and require special considerations when being worked with. They are also variable length. All of this makes them slow. unless you have a REALLY good reason for your primary key to be a numeric, use int or bigint instead. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Auto incrementing primary keys
On Feb 18, 1:14 pm, pgsql_user <[EMAIL PROTECTED]> wrote:
> On Feb 18, 6:08 pm, Paul Boddie <[EMAIL PROTECTED]> wrote:
>
>
>
> > On 18 Feb, 13:36, django_user <[EMAIL PROTECTED]> wrote:
>
> > > How can stop postgresql from incrementing the primary key value, so
> > > that even after many failed insert statements it get the next id val.
>
> > "Auto-incrementing" columns, typically implemented using the serial
> > data type [1], employ sequences.
>
> > From the manual:
>
> > "To avoid blocking of concurrent transactions that obtain numbers from
> > the same sequence, a nextval operation is never rolled back; that is,
> > once a value has been fetched it is considered used, even if the
> > transaction that did the nextval later aborts. This means that aborted
> > transactions may leave unused "holes" in the sequence of assigned
> > values. setval operations are never rolled back, either."
>
> >http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
>
> > In other words, to permit a decent level of concurrency, PostgreSQL
> > doesn't wait to see if a transaction succeeds with a value from a
> > sequence before updating the sequence. If you want to reset a sequence
> > so that it always uses the next unused value as determined by looking
> > at the table, I suppose you could do something like this:
>
> > select setval('mytable_id_seq', x) from (select max(id) as x from
> > mytable) as y;
>
> > But I doubt that you would want to do this too often in any system
> > with any reasonable level of concurrent access to the table or the
> > sequence concerned.
>
> > Paul
>
> > [1]http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE...
>
> so wouldnt I run out of ids one day, if there are lot of failed insert
> statements, lets say for every successful insert there are 50
> unsuccessful inserts, so ids would be 1, 50, 100, and once I have
> thousands of rows, I will run out of IDs ? should I use bigserial
> instead ?
>
> Thanks
In theory, yes. but the standard 4 byte integer can represent about 2
billion positive numbers so even with a lot of failed inserts you're
probably not going to run out for years.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Postgres from PHP in Leopard
On Dec 19, 11:32 am, Gordon <[EMAIL PROTECTED]> wrote: > I'm a web developer who does a lot of work in PHP. The back end > database we use is bases on Postgres. I am trying to set my new > MacBook Pro up as a development system. > > I have managed to install Postgres > fromhttp://sourceforge.net/projects/pgsqlformac/ > and can access the database on my mac with tools such as pgAdmin. I > have also enabled the bundled Apache and PHP extensions. > > However, the problem is that I can't get PHP to talk to Postgres. > Running phpinfo () on the built in PHP shows that database support > extends as MySql, Mysqli and SQLite extensions, and SQLite drivers for > PDO. What I really need is a Postgres extension for maintaining older > code and a Postgres PDO driver for new code in development. > > When setting up a similar dev system on Windows it was relatively > simple to set this all up. The extensions for PHP were provided > in .dll files that you just had to include in the php.ini file. This > doesn't seem to be the case with the MacOS bundled PHP. > > Is it possible to download .so files (I believe that's what the MacOS > equivalent to .dll files are) for the drivers and include them from > the php.ini file like on the Windows box? Or is there something else > I'm meant to be doing? Anyone? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Postgres from PHP in Leopard
I'm a web developer who does a lot of work in PHP. The back end database we use is bases on Postgres. I am trying to set my new MacBook Pro up as a development system. I have managed to install Postgres from http://sourceforge.net/projects/pgsqlformac/ and can access the database on my mac with tools such as pgAdmin. I have also enabled the bundled Apache and PHP extensions. However, the problem is that I can't get PHP to talk to Postgres. Running phpinfo () on the built in PHP shows that database support extends as MySql, Mysqli and SQLite extensions, and SQLite drivers for PDO. What I really need is a Postgres extension for maintaining older code and a Postgres PDO driver for new code in development. When setting up a similar dev system on Windows it was relatively simple to set this all up. The extensions for PHP were provided in .dll files that you just had to include in the php.ini file. This doesn't seem to be the case with the MacOS bundled PHP. Is it possible to download .so files (I believe that's what the MacOS equivalent to .dll files are) for the drivers and include them from the php.ini file like on the Windows box? Or is there something else I'm meant to be doing? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Duplicating a table row while honouring key constraints
I'm developing a web application in PHP and Postgres that will basically serve as a CMS. I want to implement a feature to allow users to make copies of documents or folders, so this will require the appropriate rows to be duplicated. If possible I'd like to do this with SQL queries and avoid SELECTing the row, munging it in PHP and INSERTING it back. I suspect that this is probably the way I'll have to go, but if it could be done entirely in SQL that would be nice. At first I thought INSERT INTO table_name SELECT * from table_name where primary_key = unique_value would do it, but that would obviously violate the primary key uniqueness constraint. I'm wondering if there's a way to do this where I only grab the data to be copied and let the database work out the new primary key itself. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] New US DST Rules & PostgreSQL
Hi- Anyone know where to find info about whether or not the new US DST rules impact certain versions of Postgres and what needs to be done to ensure observance of the new rules? Thanks. -- adam ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Writing output to a file
I am coming from Oracle & mySQL. In Oracle I can use spool foo.txt spool off After this the file foo.txt shows everything the screen showed. If I had echo on it will show the command and results. If I turn on timing it shows there too. With mySQL similarly I can use tee foo.txt and notee; I have used \o foo.txt and it seems to write significantly less than what I see on the screen. Has anyone had experience with this?-- The Gordons[EMAIL PROTECTED][EMAIL PROTECTED]
Re: [GENERAL] INSERT OR UPDATE?
>I am writing an app in PHP that uses a PostGres database.
>One thing i have noticed is that what should/could be a single line of
>SQL code takes about 6 lines of PHP. This seem wasteful and redundant
>to me.
>
>Here is a sample of what I'm talking about ($db is a PDO already
>defined and created).
>
>$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
>somecondition";
>$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
>if(!$db->query($query[1])){
>$db->query($query[2]);
>}
>
>What I'm curious to know is if there is some way to simplify this,
>either buy some PHP builtin or extension, or possibly something in SQL
>I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
>but I can't seem to find anything relevant at all about it.
MySQL permits (but it's not standard, and available in MySQL 4.1.0
and later):
INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
KEY UPDATE somefield = '$someval';
This is very useful for times when you want to count something (e.g.
SPAM), and if a record doesn't exist, make one with a count of 1.
I don't know whether something similar is available in PostGres.
Gordon L. Burditt
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] shared_buffers + Windows
Hello I have next problem: I have Windows 2000 s. Postgresql 8.0 database have 300MB and hardware configuration (2 x P3 1Ghz,1GB RAM and SCSI HD). How set the shared_buffers and other parameters to better performance. for example: When i read table from workstation (20.000 record and 20 column) on the Windows 2000 s. - 40 sec. on the Debian (P 8.0) the same DB, the same Table - 3 sec. Kris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] shared_buffers + Windows
max_connections = 50 on my server ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] VACUUM ANALYZE -vs- ANALYZE on an insert-only table.
If I have a table that I only use for INSERTs and queries (no UPDATEs or DELETEs), is it enough to just run ANALYZE on the table instead of VACUUM ANALYZE? In other words, is running a VACUUM on a table useful if all that you're doing is INSERTing into it? My understanding of VACUUM is that it cleans up stale tuples that are left after UPDATEs and DELETEs. The reason that I'm asking this has mainly to do with performance. I've got a lot of large tables (> 1 million records) in my database. Running a nightly VACUUM ANALYZE takes a while (> 1 hour) to run. I'm wondering, given the condition above, if I can skip the VACUUM part for these large tables and just run ANALYZE. If it matters, we're currently using Postgres 7.2.1. -Matt. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] DeadLocks
This is my first posting to this site, but it's my last resort. We're running 7.0.3. I have 20 servlets that make use of our postgres database. All of the programs are able to perform their assigned SQL tasks, but not on a consistent basis. Often and unpredictably, different programs performing different functions (add, delete, update) will essentially hang. I will view the process (ps -ef) and postgres will be in an (commit, delete or update) state but does not finish. All others wishing to perform a task either via the browser or at command line (psql) will hang. The only way I can free up the process is having our sysadmin "kill" the process. The ten tables contain less than 400 rows. We are in test mode and will go into production after this "show stopper" is resolved. I have run "vacuum" which also hangs on one table that contain 382 rows. I am the sole tester, so I am not clashing with other persons. The servlets each make their own connection using the postgres' jdbc driver. The connections are closed at the end of the program. These programs are very simple insert, update, select statements. Has anybody else had this problem? What debugging methods/tools/logs will help. This is a very small development shop, so I'm throwing this out to a larger community for the first time. Thanks, Gordon Campbell [EMAIL PROTECTED] Educational Technologist 212-854-1869
[GENERAL] Re: DB2 on Linux beats MS where would postgres end up?
In article <033f01c0de0d$ce93fcc0$230470d1@INSPIRON>, "Dave Cramer" <[EMAIL PROTECTED]> wrote: > http://www.zdnet.com/zdnn/stories/news/0,4586,2760874,00.html?chkpt=zdnn0516 > 01 > > It would be great if we could see where postgres fits in this benchmark There are lies, damned lies, and benchmarks. That said (or shamelessly cribbed from Disraeli), I have found that for my current application (an auditing system for the transportation industry), PostgreSQL is 2-4 times faster than DB2 UDB 7.1 for most of our queries. To say that I was suprised is an understatement (no offense to the PostgreSQL crew). The database has a couple dozen tables, the largest is just over 1GB with 3.5 million rows. The database as a whole is over 6GB. This is running PostgreSQL 7.1 under RedHat 7.1 (it was true under RH 6.2, also). Hardware is an IBM Netfinity 7000 (4xPPro200/1M) with 1.5GB RAM and two RAID-5E arrays. My customer is running on a Dell PowerEdge 2400 (2xPIII 866) with 512MB RAM with a RAID-1 and a RAID-10 array. This one is amazingly fast! As always, your mileage may vary, contents may have settled during shipment, and objects in mirror are closer than they appear. Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] RE: MS SQL 7.0 to PostgreSQL 7.1
In article <08CD1781F85AD4118E0800A0C9B8580B094A74@NEZU>, "Jeff Eckermann" <[EMAIL PROTECTED]> wrote: > Also: > * Watch out for embedded tabs and carriage returns in your data: these > will cause problems during or after your COPY into PostgreSQL. * Check > the value used in the exported file to represent NULL values (could be > an empty string, or "NULL", or something else), and use that in your > COPY statement: "COPY table from stdin with null as 'whatever';" My experience is that MSSQL7 will export (bcp) NULLs as an ASCII zero character. Not pretty. You'll want to put together a little PERL script to fix that. Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How much log space does VACUUM need?
Hello all, I'm running 7.1RC2 and have a question/problem: I have a table which is 28150 pages in size. It has two indices of 8001 and 9750 pages. The filesystem on which pg_xlog resides has ~750MB free. No other PostgreSQL work is running. Yet, when running VACUUM ANALYZE on this table, I run out of space. Here's the snippet where it breaks: DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: Rel booking: Pages: 28150 --> 22814; Tuple(s) moved: 146717. CPU 168.87s/229.66u sec. FATAL 2: ZeroFill(/data00/pgdata/pg_xlog/xlogtemp.29381) failed: No space left on device Server process (pid 29381) exited with status 512 at Thu Apr 5 23:06:52 2001 Terminating any active server processes... Server processes were terminated at Thu Apr 5 23:06:52 2001 Reinitializing shared memory and semaphores DEBUG: database system was interrupted at 2001-04-05 23:05:59 EDT DEBUG: CheckPoint record at (0, 3111073188) DEBUG: Redo record at (0, 3103812936); Undo record at (0, 2332033096); Shutdown FALSE DEBUG: NextTransactionId: 846; NextOid: 846116 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (0, 3103812936) DEBUG: open(logfile 0 seg 188) failed: No such file or directory DEBUG: redo done at (0, 3154111604) FATAL 2: ZeroFill(/data00/pgdata/pg_xlog/xlogtemp.29468) failed: No space left on device /opt/postgresql/bin/postmaster: Startup proc 29468 exited with status 512 - abort It appears the VACUUM ANALYZE requires >3x the table size in order to run? I don't recall seeing this behaviour on my other box, but that my just be because it has an obscene amount of free space on the $PGDATA filesystem. Thanks, Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: php Compile question
In article <[EMAIL PROTECTED]>, "Joseph" <[EMAIL PROTECTED]> wrote: > I am switching from rpm install of postgres to the compiled version. I > have this running fine, but now my php4 quit working. > > So I am trying to compile it and have the error that it cannot find > postgres.h > > Does it need to look for another file or do I need to tell it where to > look for it? I had that problem too, as recently as RC2. I copied the file myself, as well as the util/ directory (in src/include/), which is also needed. Not sure why make install doesn't do it... Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] 7.1b6 - pg_xlog filled fs, postmaster won't start
In article <[EMAIL PROTECTED]>, "Mikheev, Vadim" <[EMAIL PROTECTED]> wrote: >> Is it OK to delete the files from pg_xlog? What will be the result? > It's not Ok. Though you could remove files numbered from 000 > to 00012 (in hex), if any. OK, thanks. Is there any documentation on these files, and what our options are if something like this happens? >> Will I be able to avoid this problem by splitting the load data into >> multiple files? > Yes if you'll run CHECKPOINT command between COPY-s. You could also > move logs to another FS. Vadim I have the logs in /home/pgsqldata, and created another location in /home2/pgsqldata for the database. Still managed to fill it up. It's a *big* file. With other RDBMS products I use, DB2 and Sybase, there are options in the import/load/bcp utilities which commit every n records, selectable by the user. I think having a feature like this in COPY would greatly facilitate data migrations (which is what I'm doing, and the reason for such a big file). What do you think? Thanks, Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] 7.1b6 - pg_xlog filled fs, postmaster won't start
Yes, I was loading a large table. :-) The filesystem with pg_xlog filled up, and the backend (all backends) died abnormally. I can't restart postmaster, either. There are no stray IPC resources left allocated. Is it OK to delete the files from pg_xlog? What will be the result? Will I be able to avoid this problem by splitting the load data into multiple files? Here's some information from the log: - DEBUG: copy: line 1853131, XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: copy: line 1867494, XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: copy: line 1884676, XLogWrite: new log file created - consider increasing WAL_FILES FATAL 2: copy: line 1897094, ZeroFill(logfile 0 seg 196) failed: No space left on device Server process (pid 7867) exited with status 512 at Tue Mar 20 18:16:27 2001 Terminating any active server processes... NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. Server processes were terminated at Tue Mar 20 18:16:27 2001 Reinitializing shared memory and semaphores DEBUG: database system was interrupted at 2001-03-20 18:16:03 EST DEBUG: CheckPoint record at (0, 3274567656) DEBUG: Redo record at (0, 3271658024); Undo record at (0, 1464223896); Shutdown FALSE DEBUG: NextTransactionId: 1639; NextOid: 432 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (0, 3271658024) DEBUG: open(logfile 0 seg 196) failed: No such file or directory DEBUG: redo done at (0, 3288327848) FATAL 2: ZeroFill(logfile 0 seg 196) failed: No space left on device /opt/postgresql/bin/postmaster: Startup proc 7922 exited with status 512 - abort - Thanks, Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] 7.1b6 - pg_xlog filled fs, postmaster won't start
Yes, I was loading a large table. :-) The filesystem with pg_xlog filled up, and the backend (all backends) died abnormally. I can't restart postmaster, either. Is it OK to delete the files from pg_xlog? What will be the result? Will I be able to avoid this problem by splitting the load data into multiple files? Here's some information from the log: - DEBUG: copy: line 1853131, XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: copy: line 1867494, XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: copy: line 1884676, XLogWrite: new log file created - consider increasing WAL_FILES FATAL 2: copy: line 1897094, ZeroFill(logfile 0 seg 196) failed: No space left on device Server process (pid 7867) exited with status 512 at Tue Mar 20 18:16:27 2001 Terminating any active server processes... NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. Server processes were terminated at Tue Mar 20 18:16:27 2001 Reinitializing shared memory and semaphores DEBUG: database system was interrupted at 2001-03-20 18:16:03 EST DEBUG: CheckPoint record at (0, 3274567656) DEBUG: Redo record at (0, 3271658024); Undo record at (0, 1464223896); Shutdown FALSE DEBUG: NextTransactionId: 1639; NextOid: 432 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (0, 3271658024) DEBUG: open(logfile 0 seg 196) failed: No such file or directory DEBUG: redo done at (0, 3288327848) FATAL 2: ZeroFill(logfile 0 seg 196) failed: No space left on device /opt/postgresql/bin/postmaster: Startup proc 7922 exited with status 512 - abort - Thanks, Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] COPY problem
In article <[EMAIL PROTECTED]>, "Creager, Robert S" <[EMAIL PROTECTED]> wrote: > I think this is a question regarding the backend, but... [snip] > (COPY u FROM stdin). The backend process which handles the db connection > decides that it needs a whole lot of memory, although in a nice > controlled manner. The backend starts with using 6.5Mb, and at 25000 > records copied, it's taken 10Mb and has slowed down substantially. > Needless to say, this COPY will not finish before running out of memory > (estimated 300Mb). When executing the COPY to the loc table, this > problem does not occur. Am I going to have to resort to inserts for the > referring tables? I can't answer the backend question, but how about running 'split' on the big file, then COPYing these smaller files? Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(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] Migrate from MS SQL 6.5 to postgres??
In article <[EMAIL PROTECTED]>, "Unknown" <[EMAIL PROTECTED]> wrote: > I have an idea that might help I found ODBC to be very slow for > importing data So I wrote a program in C that reads in dump files of SQL > text on the Linux server itself E.G. first line is a create table, next > lines are all the insert's This is very fast, 80mb of data in about 15 > minutes Only problem is the text files need to be formatted a bit > specially If you can write a program in say VB to create the text files > (one per table) it could work. If you are interested I could forward my > C program and Foxpro prg that creates the text files that you could > convert to VB Why make it so difficult? SQL Server provides a perfectly usable bulk copy utility (bcp.exe), which will haul the data out ready-to-go. H:\tmp> bcp dbname..tabname out filename.del -c -t "|" -r "\n" \ -S server -U user -P password This will pull the data out, with '|' as the field delimiter and a newline as a record separator. Now you can COPY the data in using '|' as the delimiter. If you have BLOB data types, those tables will have to be handled in another way, of course. Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
