Re: [GENERAL] Upgrade questions
On 03/13/12 8:41 PM, Carson Gross wrote: Does anyone have a reasonable guess as to the inserts per second postgres is capable of these days on middle-of-the-road hardware? Any order of magnitude would be fine: 10, 100, 1000, 10,000. my dedicated database server in my lab, which is a 2U dual Xeon X5660 box with 12 cores at 2.8ghz, 48GB ram, and 20 15000rpm SAS drives in a RAID10 with a 1GB flash-cached raid card, can pretty easily sustain 6000 or more writes/second given enough threads doing the work, although indexes, and/or large rows would slow that down.a single connection/thread will not get that much throughput. thats my definition of a middle of the road database server. I have no idea what yours is. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Interesting article, Facebook woes using MySQL
On Tue, Mar 13, 2012 at 01:22:18AM +0100, Stefan Keller wrote: Hi all 2011/7/12 Chris Travers chris.trav...@gmail.com: I am not convinced that VoltDB is a magic bullet either. I don't I have the chance to help preparing an interview with Mike Stonebreaker to be published at www.odbms.org I'd really like to know, if he is up-to-date how Postgres performs these days and how he thinks how VoltDB overcame the overhead he claims to exist in old elephants. Do you all have more questions to Mike? I'm curious what he thinks about the role of the optimiser. IME postgresql wins for my workloads simply because PostgreSQL is smart enough to perform the joins in the right order and use the right indexes. MySQL seems to have some heuristics which are wrong just often enough to be irritating. Oh yeah, and it doesn't have CREATE INDEX CONCURRENTLY, that's *really* annoying. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
[GENERAL] Ways to edit users and permissions for database
Hi, this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and create users and permissions through libpq or SQL directly. I also found there to be a reference on the INFORMATION_SCHEMA, but still couldn't make sense out of these tables in the given context. This is to make a user administration inside the client frontend. What approach would be recommended for this purpose? Thanks Alex -- 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] Ways to edit users and permissions for database
On 03/14/12 12:38 AM, Alexander Reichstadt wrote: this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and create users and permissions through libpq or SQL directly. I also found there to be a reference on the INFORMATION_SCHEMA, but still couldn't make sense out of these tables in the given context. This is to make a user administration inside the client frontend. What approach would be recommended for this purpose? SQL commands like... CREATE USER freddy WITH PASSWORD 'something'; CREATe DATABASE freddb OWNER freddy; issued same as any other SQL queries, via libpq etc. note, the INFORMATION_SCHEMA is read only as its all implemented as VIEW's... -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Ways to edit users and permissions for database
Thanks, creation works fine, but how do I read existing permissions through SQL, is there some SELECT-statement I can use? Am 14.03.2012 um 08:43 schrieb John R Pierce: On 03/14/12 12:38 AM, Alexander Reichstadt wrote: this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and create users and permissions through libpq or SQL directly. I also found there to be a reference on the INFORMATION_SCHEMA, but still couldn't make sense out of these tables in the given context. This is to make a user administration inside the client frontend. What approach would be recommended for this purpose? SQL commands like... CREATE USER freddy WITH PASSWORD 'something'; CREATe DATABASE freddb OWNER freddy; issued same as any other SQL queries, via libpq etc. note, the INFORMATION_SCHEMA is read only as its all implemented as VIEW's... -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Frontend/Backend protocol question.
Dmitriy Igrishin wrote: According to http://www.postgresql.org/docs/9.1/static/protocol-flow.html#AEN91458 is not actually necessary for the frontend to wait for ReadyForQuery before issuing another command. But is it necessary for frontend to wait for ReadyForQuery before sending Describe message? Or is it necessary to wait for RowDescription/NoData after sending Describe before sending Query or, say, Prepare? In short, is it necessary for frontend to wait for responses on sent messages before sending a new ones? I agree with your interpretation. I have not tried it myself, but I think you can just send the next message without waiting for ReadyForQuery. The problem is that this may not make sense: for example, if you send Describe immediately after Parse, it may be that the Parse fails and the Describe does something you did not intend. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] provide pg_get_notifications()
Hi, Please provide a pg_get_notifications() function (or similar) as a complement to pg_notify(). Currently, in java jdbc, the clients must poll for notifications using getNotifications() method in org.postgresql.PGConnection. So, clients must obtain a reference to connection object and cast it down (presumably from java.sql.Connection) to org.postgresql.PGConnection. It is very problematic to not be able to use the connection as java.sql.Connection. In a typical set-up, the connection must be obtained from a ORM framework (like hibernate) which wraps the underlying connection in its own dynamic proxy. The underling connection to ORM itself may be wrapped up in another proxy by a connection pool like commons.apache.org/pool . And if you want to be fancy, the underlying connection to commons-pool may be wrapped in another proxy by connection profilers like log4jdbc/jdbmonitor/javamelody. If the programmer knows the set-up, he can of course overcome the problem by using framework-specific knowledge to obtain a PGConnection (using a lot of downcasts) or reflection tricks (not desirable). However, in some cases, the programmer cannot possibly know the set-up in advance. For example if he writes a middleware/help library. And thus the need for pg_get_notifications() function. thanks, -- 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] Ways to edit users and permissions for database
On 03/14/2012 12:59 AM, Alexander Reichstadt wrote:
Thanks, creation works fine, but how do I read existing permissions through
SQL, is there some SELECT-statement I can use?
Not sure what you want, all permissions for a user(role), permissions
for an object or some other combination but here are a few suggestions:
http://www.postgresql.org/docs/9.0/static/functions-info.html
Look at table 9-48
If you run psql with the -E switch you get the system queries that are
generated by using the various \ commands.
psql -E -d test -U aklaver
So for example finding the privileges for a table :
test= \dp big_int_test
* QUERY **
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as Type,
pg_catalog.array_to_string(c.relacl, E'\n') AS Access privileges,
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl,
E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS Column access privileges
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(big_int_test)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
+--+---+---+--
public | big_int_test | table | |
As the above indicates the query uses the system catalogs information on
which can be found here:
http://www.postgresql.org/docs/9.0/static/catalogs.html
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ways to edit users and permissions for database
Excellent, Thank you. Exactly what I was looking for.
Am 14.03.2012 um 14:26 schrieb Adrian Klaver:
On 03/14/2012 12:59 AM, Alexander Reichstadt wrote:
Thanks, creation works fine, but how do I read existing permissions through
SQL, is there some SELECT-statement I can use?
Not sure what you want, all permissions for a user(role), permissions for an
object or some other combination but here are a few suggestions:
http://www.postgresql.org/docs/9.0/static/functions-info.html
Look at table 9-48
If you run psql with the -E switch you get the system queries that are
generated by using the various \ commands.
psql -E -d test -U aklaver
So for example finding the privileges for a table :
test= \dp big_int_test
* QUERY **
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN
'sequence' END as Type,
pg_catalog.array_to_string(c.relacl, E'\n') AS Access privileges,
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS Column access privileges
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(big_int_test)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
+--+---+---+--
public | big_int_test | table | |
As the above indicates the query uses the system catalogs information on
which can be found here:
http://www.postgresql.org/docs/9.0/static/catalogs.html
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] Call for Google Summer of Code (GSoC) 2012: Project ideas?
Stefan Keller, 08.03.2012 20:40: Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the burden to look for a cool project... Any ideas? -Stefan What about an extension to the CREATE TRIGGER syntax that combines trigger definition and function definition in a single statement? Something like: CREATE TRIGGER my_trg BEFORE UPDATE ON some_table FOR EACH ROW EXECUTE DO $body$ BEGIN ... here goes the function code ... END; $body$ LANGUAGE plpgsql; which would create both objects (trigger and trigger function) at the same time in the background. The CASCADE option of DROP TRIGGER could be enhanced to include the corresponding function in the DROP as well. This would make the syntax a bit easier to handle for those cases where a 1:1 relationship exists between triggers and functions but would still allow the flexibility to re-use trigger functions in more than one trigger. Regards Thomas -- 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] Call for Google Summer of Code (GSoC) 2012: Project ideas?
Hi all, 2012/3/14 Thomas Kellerer spam_ea...@gmx.net: Stefan Keller, 08.03.2012 20:40: Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the burden to look for a cool project... Any ideas? -Stefan What about an extension to the CREATE TRIGGER syntax that combines trigger definition and function definition in a single statement? Something like: CREATE TRIGGER my_trg BEFORE UPDATE ON some_table FOR EACH ROW EXECUTE DO $body$ BEGIN ... here goes the function code ... END; $body$ LANGUAGE plpgsql; which would create both objects (trigger and trigger function) at the same time in the background. The CASCADE option of DROP TRIGGER could be enhanced to include the corresponding function in the DROP as well. This would make the syntax a bit easier to handle for those cases where a 1:1 relationship exists between triggers and functions but would still allow the flexibility to re-use trigger functions in more than one trigger. Regards Thomas Thanks to all who responded here. There are now two students here at our university and it seems that they prefer another open source project (which I support too). Let's take some these good ideas to the Postgres wiki (if there is an idea page there :-) -Stefan -- 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 questions
I felt pretty good about my server until I read this. On Wed, 2012-03-14 at 00:24 -0700, John R Pierce wrote: On 03/13/12 8:41 PM, Carson Gross wrote: Does anyone have a reasonable guess as to the inserts per second postgres is capable of these days on middle-of-the-road hardware? Any order of magnitude would be fine: 10, 100, 1000, 10,000. my dedicated database server in my lab, which is a 2U dual Xeon X5660 box with 12 cores at 2.8ghz, 48GB ram, and 20 15000rpm SAS drives in a RAID10 with a 1GB flash-cached raid card, can pretty easily sustain 6000 or more writes/second given enough threads doing the work, although indexes, and/or large rows would slow that down.a single connection/thread will not get that much throughput. thats my definition of a middle of the road database server. I have no idea what yours is. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 questions
Heh. OK, so I'll plan on about 100 writes per second... *gulp* Thanks a bunch for the info guys. Cheers, Carson On Wed, Mar 14, 2012 at 7:54 AM, Bret Stern bret_st...@machinemanagement.com wrote: I felt pretty good about my server until I read this. On Wed, 2012-03-14 at 00:24 -0700, John R Pierce wrote: On 03/13/12 8:41 PM, Carson Gross wrote: Does anyone have a reasonable guess as to the inserts per second postgres is capable of these days on middle-of-the-road hardware? Any order of magnitude would be fine: 10, 100, 1000, 10,000. my dedicated database server in my lab, which is a 2U dual Xeon X5660 box with 12 cores at 2.8ghz, 48GB ram, and 20 15000rpm SAS drives in a RAID10 with a 1GB flash-cached raid card, can pretty easily sustain 6000 or more writes/second given enough threads doing the work, although indexes, and/or large rows would slow that down.a single connection/thread will not get that much throughput. thats my definition of a middle of the road database server. I have no idea what yours is. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Alter Column from inet to inet[]
Hi,I need to change a column type from inet to inet[] but the alter command always gives me the following errors ERROR: column access_ip cannot be cast to type inet[] ALTER TABLE users ALTER COLUMN access_ip SET DATA TYPE inet[] USING access_ip::inet[]; Tried various but no luck. Couldn't find anything in the manuals either. Can anyone point me in the right direction or tell me what's wrong with my statement. Thanks for any help. Alex
Re: [GENERAL] Alter Column from inet to inet[]
Alex - ainto...@hotmail.com writes: Hi,I need to change a column type from inet to inet[] but the alter command always gives me the following errors ERROR: column access_ip cannot be cast to type inet[] ALTER TABLE users ALTER COLUMN access_ip SET DATA TYPE inet[] USING access_ip::inet[]; The problem is precisely that the system lacks a cast from inet to inet[]. Telling it to apply a cast it hasn't got doesn't help. What you need to do is show how to construct the new column values. Try something like USING ARRAY[access_ip] 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] COPY and indices?
Le mardi 13 mars 2012 à 11:15, Merlin Moncure a écrit : 2012/3/13 François Beausoleil franc...@teksol.info (mailto:franc...@teksol.info): I'll go with the COPY, since I can live with the batched requirements just fine. 30-40 'in transaction' i/o bound inserts is so slow as to not really be believable unless each record is around 1 megabyte because being in transaction removes storage latency from the equation. Even on a crappy VM. As a point of comparison my sata workstation drive can do in the 10s of thousands. How many records are you inserting per transaction? I took the time to gather statistics about the database server: https://gist.github.com/07bbf8a5b05b1c37a7f2 The files are a series of roughly 30 second samples, while the system is under production usage. When I quoted 30-40 transactions per second, I was actually referring to the number of messages processed from my message queue. Going by the PostgreSQL numbers, xact_commit tells me I manage 288 commits per second. It's much better than I anticipated. Anyways, if anybody has comments on how I could increase throughput, I'd appreciate. My message queues are almost always backed up by 1M messages, and it's at least partially related to PostgreSQL: if the DB can write faster, I can manage my backlog better. I'm still planning on going with batch processing, but I need to do something ASAP to give me just a bit more throughput. Thanks! François -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Searching email, Full Text Search prefix, not expected results
Hi guys, I'm going to crazy about FTS with prefix agains email values on
tsvector. Follow how to reproduce:
For the next tsvector:
=# select to_tsvector('u...@company.com')
to_tsvector
--
'u...@company.com':1
I expects TRUE for all next tsqueryes:
select to_tsvector('u...@company.com') @@ to_tsquery('u:*');
select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
select to_tsvector('u...@company.com') @@ to_tsquery('use:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@com:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compa:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compan:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:*');
But NOT, there are some NOT expected and confusing me results:
=# select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
?column?
--
t
(1 row)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
?column?
--
t
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
?column?
--
t
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:
*');
?column?
--
t TRUE OOhHHH I'm going crazy!!!
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:
*');
?column?
--
t TRUE ... Yes I'm crazy.
Please some ligths about it.
(I follow the official docs in
http://www.postgresql.org/docs/9.1/interactive/textsearch.html for my
knowledge)
Thx!
Re: [GENERAL] Upgrade questions
On 03/14/12 12:24 AM, John R Pierce wrote: thats my definition of a middle of the road database server. I have no idea what yours is. let me add... this server was under $7000 plus the disk drives (it actually has 25 drives, 20 are in the raid10 used for the database testing).we built this specifically to compare against 'big iron' RISC unix servers like IBM Power7 and Sun^W Oracle Sparc stuffs with SAN storage, which frequently end up deep into the 6 digit price range. as a 2-socket Intel 2U server goes, its fairly high end, but there's 4 socket and larger systems out there, as well as the monster RISC stuff where 64 or 128 CPU cores is not unheard of, and 100s of GB of ram. * HP DL180G6 * dual Xeon X5660 6c 2.8Ghz * 48GB ECC ram * p411i 1GB flash-backed RAID card * 25 bay 2.5 SAS2 backplane (this is an option on this server chassis, and means no DVD/CD) -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] COPY and indices?
2012/3/14 François Beausoleil franc...@teksol.info: Le mardi 13 mars 2012 à 11:15, Merlin Moncure a écrit : 2012/3/13 François Beausoleil franc...@teksol.info (mailto:franc...@teksol.info): I'll go with the COPY, since I can live with the batched requirements just fine. 30-40 'in transaction' i/o bound inserts is so slow as to not really be believable unless each record is around 1 megabyte because being in transaction removes storage latency from the equation. Even on a crappy VM. As a point of comparison my sata workstation drive can do in the 10s of thousands. How many records are you inserting per transaction? I took the time to gather statistics about the database server: https://gist.github.com/07bbf8a5b05b1c37a7f2 The files are a series of roughly 30 second samples, while the system is under production usage. When I quoted 30-40 transactions per second, I was actually referring to the number of messages processed from my message queue. Going by the PostgreSQL numbers, xact_commit tells me I manage 288 commits per second. It's much better than I anticipated. Anyways, if anybody has comments on how I could increase throughput, I'd appreciate. My message queues are almost always backed up by 1M messages, and it's at least partially related to PostgreSQL: if the DB can write faster, I can manage my backlog better. I'm still planning on going with batch processing, but I need to do something ASAP to give me just a bit more throughput. well your iowait numbers are through the roof which makes things pretty simple from a diagnosis point of view: your storage is overloaded. the only remedies are to try and make your queries more efficient so that you are doing less writing, better use of transactions, etc. but looking at the log it appears the low hanging fruit is already grabbed (synchronous_commit=off, etc). so you have to choose from a list of not very pleasant options: *) fsync=off *) tune the application *) bring more/faster storage online. a single ssd would probably make your problem disappear. in the vm world, hopefully you can at least bring another volume online and move your wal to that. *) HARDWARE. In the entirety of my career, I have never found anything more perplexing than the general reluctance to upgrade hardware to solve hardware related performance bottlenecks. Virtualization is great technology but is nowhere near good enough in my experience to handle high transaction rate database severs. A 5000$ server will solve your issue, and you'll spend that in two days scratching your head trying to figure out the issue (irritating your customers all the while). merlin -- 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 questions
On 03/14/2012 12:04 PM, John R Pierce wrote: On 03/14/12 12:24 AM, John R Pierce wrote: thats my definition of a middle of the road database server. I have no idea what yours is. let me add... this server was under $7000 plus the disk drives (it actually has 25 drives... My car was only $5,000...plus the engine and transmission. :) I was just looking at some modest-sized 15k SAS drives that priced out in the $400-550 range. 25 of them would add a minimum of $10,000 to the price tag. Still under 6-figures, though. Cheers, Steve -- 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] COPY and indices?
Le mercredi 14 mars 2012 à 15:06, Merlin Moncure a écrit : 2012/3/14 François Beausoleil franc...@teksol.info (mailto:franc...@teksol.info): In the entirety of my career, I have never found anything more perplexing than the general reluctance to upgrade hardware to solve hardware related performance bottlenecks. Virtualization is great technology but is nowhere near good enough in my experience to handle high transaction rate database severs. A 5000$ server will solve your issue, and you'll spend that in two days scratching your head trying to figure out the issue (irritating your customers all the while). Thank you for your analysis, Merlin. I already suspected as much. Have a great day! François -- 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 questions
On 03/14/12 12:16 PM, Steve Crawford wrote: I was just looking at some modest-sized 15k SAS drives that priced out in the $400-550 range. 25 of them would add a minimum of $10,000 to the price tag. Still under 6-figures, though. those disks aren't any cheaper when they are behind a EMC or NetApp SAN/NAS... in fact, most any of the 'big name' enterprise storage vendors would charge about triple that for each disk. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 questions
On Wed, Mar 14, 2012 at 1:41 PM, John R Pierce pie...@hogranch.com wrote: On 03/14/12 12:16 PM, Steve Crawford wrote: I was just looking at some modest-sized 15k SAS drives that priced out in the $400-550 range. 25 of them would add a minimum of $10,000 to the price tag. Still under 6-figures, though. those disks aren't any cheaper when they are behind a EMC or NetApp SAN/NAS... in fact, most any of the 'big name' enterprise storage vendors would charge about triple that for each disk. Note that if you don't need a lot of storage you can often use 300G 15k SAS drives which are around $300 each. 20 of those in a RAID-10 gives you ~3TB of storage which is plenty for most transactional applications. -- 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 questions
On 03/14/12 12:53 PM, Scott Marlowe wrote: Note that if you don't need a lot of storage you can often use 300G 15k SAS drives which are around $300 each. 20 of those in a RAID-10 gives you ~3TB of storage which is plenty for most transactional applications. I'm actually using 25 x 146gb 15k SAS2 as we didn't need space, just speed, so the 20xraid10 is 1.4TB. most of our database tests to date have been in the 50-100GB range. and they shread at IOPS. the controller and/or IO channels seems to bottleneck somewhere up around 1.2GB/sec sustained write, or at about 12000 write IOPS. afaik, the HP P411 raid card is a LSI Logic megasas2 card with HP firmware, the hardware is equivalent to the LSI 9260-8i. the HP firmware is somewhat less annoying than the LSI megacli stuff. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Searching email, Full Text Search prefix, not expected results
=?ISO-8859-1?Q?Daniel_V=E1zquez?= daniel2d2...@gmail.com writes: Hi guys, I'm going to crazy about FTS with prefix agains email values on tsvector. tsvector is meant for searching for natural-language words. It is not a good idea to imagine that it works exactly like a substring match, especially on strings that aren't simple words. (Frankly, I think the prefix feature is a piece of junk, precisely because it encourages people to think cases like this will work.) Possibly you'd be happier with trigrams ... 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
[GENERAL] pg_role vs. pg_shadow or pg_user
Hi, in the documentation of 8.1 the concept of roles is outlined compared to users and groups at http://www.postgresql.org/docs/8.1/static/user-manag.html. I am running 9.1 and due to currently learning about the ins and outs of users and permissions in postgres as opposed to mysql, and because of needing to read system tables, I also read today that pg_shadow is the real table containing the users as opposed to pg_user which is only a view and one never displaying anything but for the password. I don't have the link where that was, but anyways, this lead me to check: PW=# select * FROM pg_catalog.pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd| valuntil | useconfig --+--+-+--+---+-+-+--+--- postgres | 10 | t | t| t | t | md5d63999e27600a80bb728cc0d7c2d6375 | | testa|24761 | f | f| f | f | md52778dfab33f8a7197bce5dfaf596010f | | (2 rows) PW=# select * FROM pg_catalog.pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid --+--++---+-+--+-++--+-+---+---+--- postgres | t| t | t | t | t | t | t | -1 | | | |10 testa| f| t | f | f | f | t | f | -1 | | | | 24761 abcd | f| t | f | f | f| f | f | -1 | | | | 24762 testb| f| t | f | f | f | f | f | -1 | | | | 24763 (4 rows) ^ PW=# select * FROM pg_catalog.pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig --+--+-+--+---+-+--+--+--- postgres | 10 | t | t| t | t | | | testa|24761 | f | f| f | f | | | (2 rows) Why is there a difference in these tables? Shouldn't pg_user, pg_shadow and pg_roles have entries where usename equals rolename and moreover should contain the same amount of entries? testb was created doing create role testb with role testa I was assuming that this would sort of clone the settings of testa into a new user testb. testa was created using create user. Regards Alex
Re: [GENERAL] pg_role vs. pg_shadow or pg_user
You only get pg_shadow entries for roles that can login (rolcanlogin = true). CREATE ROLE defaults to NO LOGIN. CREATE USER defaults to LOGIN. See http://www.postgresql.org/docs/9.1/interactive/sql-createrole.html __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com* On Wed, Mar 14, 2012 at 16:04, Alexander Reichstadt l...@mac.com wrote: Hi, in the documentation of 8.1 the concept of roles is outlined compared to users and groups at http://www.postgresql.org/docs/8.1/static/user-manag.html. I am running 9.1 and due to currently learning about the ins and outs of users and permissions in postgres as opposed to mysql, and because of needing to read system tables, I also read today that pg_shadow is the real table containing the users as opposed to pg_user which is only a view and one never displaying anything but for the password. I don't have the link where that was, but anyways, this lead me to check: PW=# select * FROM pg_catalog.pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd| valuntil | useconfig --+--+-+--+---+-+-+--+--- postgres | 10 | t | t| t | t | md5d63999e27600a80bb728cc0d7c2d6375 | | testa|24761 | f | f| f | f | md52778dfab33f8a7197bce5dfaf596010f | | (2 rows) PW=# select * FROM pg_catalog.pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid --+--++---+-+--+-++--+-+---+---+--- postgres | t| t | t | t | t | t | t | -1 | | | |10 testa| f| t | f | f | f | t | f | -1 | | | | 24761 abcd | f| t | f | f | f | f | f | -1 | | | | 24762 testb| f| t | f | f | f | f | f | -1 | | | | 24763 (4 rows) ^ PW=# select * FROM pg_catalog.pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig --+--+-+--+---+-+--+--+--- postgres | 10 | t | t| t | t | | | testa|24761 | f | f| f | f | | | (2 rows) Why is there a difference in these tables? Shouldn't pg_user, pg_shadow and pg_roles have entries where usename equals rolename and moreover should contain the same amount of entries? testb was created doing *create role testb with role testa* * * I was assuming that this would sort of clone the settings of testa into a new user testb. testa was created using create user. Regards Alex
Re: [GENERAL] pg_role vs. pg_shadow or pg_user
Alexander Reichstadt l...@mac.com writes: in the documentation of 8.1 the concept of roles is outlined compared to users and groups at http://www.postgresql.org/docs/8.1/static/user-manag.html. Um ... why are you reading 8.1 documentation while running 9.1? There are likely to be some obsolete things in there. I also read today that pg_shadow is the real table containing the users as opposed to pg_user which is only a view and one never displaying anything but for the password. I don't have the link where that was, Whereever it was, it was even more obsolete than the 8.1 docs. pg_shadow has been a view (on pg_authid) for quite a while now. Try \d+ pg_shadow in psql. The reason this is such a mess is that we've changed the catalog representation several times, each time leaving behind a view that was meant to emulate the old catalog. For some time now, pg_authid has been the ground truth, but it stores entries for both login and non-login roles, which more or less correspond to what used to be users and groups. pg_roles is the only non-protected view that shows you all the entries. 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] Upgrade questions
On Wed, Mar 14, 2012 at 2:34 PM, John R Pierce pie...@hogranch.com wrote: On 03/14/12 12:53 PM, Scott Marlowe wrote: Note that if you don't need a lot of storage you can often use 300G 15k SAS drives which are around $300 each. 20 of those in a RAID-10 gives you ~3TB of storage which is plenty for most transactional applications. I'm actually using 25 x 146gb 15k SAS2 as we didn't need space, just speed, so the 20xraid10 is 1.4TB. most of our database tests to date have been in the 50-100GB range. and they shread at IOPS. the controller and/or IO channels seems to bottleneck somewhere up around 1.2GB/sec sustained write, or at about 12000 write IOPS. afaik, the HP P411 raid card is a LSI Logic megasas2 card with HP firmware, the hardware is equivalent to the LSI 9260-8i. the HP firmware is somewhat less annoying than the LSI megacli stuff. And don't forget that if you need way less than the 1.5 to 3TB mentioned earlier, you can short stroke the drives to use the fastest parts of the platters and reduce seek times even more. And yeah, anything is less annoying than megacli. The fact that their GUI / BIOS interface is just as horrific, if not moreso, than megacli is a huge turn off for me with LSI. If you've ever used the web interface on the OOB ethernet interface on an Areca, you're hooked. The fact that it can send emails on its own etc is just frosting on the cake. -- 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] Searching email, Full Text Search prefix, not expected results
But tsvector recognices email format as natural. And I'm not looking for a
substring. Please see the queries are incremental with the search string,
and see last four results ... I think some think it's no working properly.
El 14 de marzo de 2012 19:05, Daniel Vázquez daniel2d2...@gmail.comescribió:
Hi guys, I'm going to crazy about FTS with prefix agains email values on
tsvector. Follow how to reproduce:
For the next tsvector:
=# select to_tsvector('u...@company.com')
to_tsvector
--
'u...@company.com':1
I expects TRUE for all next tsqueryes:
select to_tsvector('u...@company.com') @@ to_tsquery('u:*');
select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
select to_tsvector('u...@company.com') @@ to_tsquery('use:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@com:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compa:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compan:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:
*');
But NOT, there are some NOT expected and confusing me results:
=# select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
?column?
--
t
(1 row)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
?column?
--
t
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
?column?
--
t
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.
:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c
:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:
*');
?column?
--
t TRUE OOhHHH I'm going crazy!!!
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:
*');
?column?
--
t TRUE ... Yes I'm crazy.
Please some ligths about it.
(I follow the official docs in
http://www.postgresql.org/docs/9.1/interactive/textsearch.html for my
knowledge)
Thx!
--
Daniel Vázquez
SICONET (A Bull Group Company)
Torre Agbar. Avda. Diagonal, 211 - planta 23
08018 - Barcelona
telf: + 34 93 2272727 (Ext. 2952)
fax: + 34 93 2272728
www.bull.es - www.siconet.es
daniel.vazq...@bull.es
Re: [GENERAL] xlog corruption
On Mon, 2012-02-27 at 16:30 -0800, Jameison Martin wrote: I'd like to get some clarification around an architectural point about recovery. I see that it is normal to see unexpected pageaddr errors during recovery because of the way Postgres overwrites old log files, and thus this is taken to be a normal termination condition, i.e. the end of the log (see http://doxygen.postgresql.org/xlog_8c.html#a0519e464bfaa79bde3e241e6cff986c7). My question is how does recovery distinguish between the actual end of the log as opposed to a log file corruption (e.g. torn page)? I'd like to be able to distinguish between a corruption in the log vs. a normal recovery condition if possible. If you have a power failure, a torn page in the WAL is expected. Torn pages in the data pages are fixed up using WAL; but WAL doesn't have anything under it to prevent/fix torn pages (unless your filesystem prevents them). Of course, checksums are used to prevent recovery from attempting to play a partial or otherwise corrupt WAL record. What kind of corruption are you trying to detect? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] copy in date string 00-00-00 00:00:00
I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg copy table from file csv header statement to load the tables. There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time component only. The empty values are being output as 00-00-00 00:00:00. The import is falling over on rows that contain these zero dates. I can adjust the NLS session format of the date string, within a small range, in the oracle environment. However, each form I have attempted still results in these zero date values in the csv file. I am thinking of run the csv files through a filter to change the 00-00-00 00:00:00 to an empty value. Is there a way for postgres to handle this? - Mark -- 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] copy in date string 00-00-00 00:00:00
On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips mark.phill...@mophilly.com wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg copy table from file csv header statement to load the tables. There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time component only. The empty values are being output as 00-00-00 00:00:00. The import is falling over on rows that contain these zero dates. I can adjust the NLS session format of the date string, within a small range, in the oracle environment. However, each form I have attempted still results in these zero date values in the csv file. I am thinking of run the csv files through a filter to change the 00-00-00 00:00:00 to an empty value. Is there a way for postgres to handle this? Can you run it through sed and replace the -00-00 00:00:00 to NULL (no quotes) ? That should work. -- 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] copy in date string 00-00-00 00:00:00
On 03/14/2012 08:16 PM, Scott Marlowe wrote: On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips mark.phill...@mophilly.com wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg copy table from file csv header statement to load the tables. There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time component only. The empty values are being output as 00-00-00 00:00:00. The import is falling over on rows that contain these zero dates. I can adjust the NLS session format of the date string, within a small range, in the oracle environment. However, each form I have attempted still results in these zero date values in the csv file. I am thinking of run the csv files through a filter to change the 00-00-00 00:00:00 to an empty value. Is there a way for postgres to handle this? Can you run it through sed and replace the -00-00 00:00:00 to NULL (no quotes) ? That should work. I think COPY (depending on arguments) uses \N by default. Another option is to pull it into a temp table and make fix it up from there. -Andy -- 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] copy in date string 00-00-00 00:00:00
On 03/14/2012 08:32 PM, Andy Colson wrote: On 03/14/2012 08:16 PM, Scott Marlowe wrote: On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips mark.phill...@mophilly.com wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg copy table from file csv header statement to load the tables. There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time component only. The empty values are being output as 00-00-00 00:00:00. The import is falling over on rows that contain these zero dates. I can adjust the NLS session format of the date string, within a small range, in the oracle environment. However, each form I have attempted still results in these zero date values in the csv file. I am thinking of run the csv files through a filter to change the 00-00-00 00:00:00 to an empty value. Is there a way for postgres to handle this? Can you run it through sed and replace the -00-00 00:00:00 to NULL (no quotes) ? That should work. I think COPY (depending on arguments) uses \N by default. Another option is to pull it into a temp table and make fix it up from there. -Andy humm.. and speaking of arguments, Mark, did you check the help? where option can be one of: NULL 'null_string' so, perhaps just: COPY tbl from 'dump.csv' with NULL '00-00-00 00:00:00'; -Andy -- 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] copy in date string 00-00-00 00:00:00
On Wed, Mar 14, 2012 at 7:37 PM, Andy Colson a...@squeakycode.net wrote: On 03/14/2012 08:32 PM, Andy Colson wrote: On 03/14/2012 08:16 PM, Scott Marlowe wrote: On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips mark.phill...@mophilly.com wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg copy table from file csv header statement to load the tables. There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time component only. The empty values are being output as 00-00-00 00:00:00. The import is falling over on rows that contain these zero dates. I can adjust the NLS session format of the date string, within a small range, in the oracle environment. However, each form I have attempted still results in these zero date values in the csv file. I am thinking of run the csv files through a filter to change the 00-00-00 00:00:00 to an empty value. Is there a way for postgres to handle this? Can you run it through sed and replace the -00-00 00:00:00 to NULL (no quotes) ? That should work. I think COPY (depending on arguments) uses \N by default. Another option is to pull it into a temp table and make fix it up from there. -Andy humm.. and speaking of arguments, Mark, did you check the help? where option can be one of: NULL 'null_string' so, perhaps just: COPY tbl from 'dump.csv' with NULL '00-00-00 00:00:00'; Thought of that one too, but it'll break all the other fields if they have NULLs in them. -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera vi...@khera.org wrote: I'll bet what happened was postgres re-wrote your table for you, effectively doing a compaction. You can get similar effect by doing an alter table and changing an INTEGER field to be INTEGER. Postgres does not optimize that do a no-op, so you get the re-writing effect. How does table rewriting work? Does it happen a row at a time or all at once? In other words, how much free disk space is needed on an 800 TB filesystem to rewrite a 550 TB table? (Have I got enough space?) Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Did xpath_table get dropped.
I don't see anything to that effect in the release notes I've looked at. I built 9.1.2 from source with these options. CONFIGURE = '--with-libraries=/lib:/lib64:/lib/x86_64-linux-gnu' '--with-python' '--with-openssl' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' \df xpath* List of functions ++---+--+-++ | Schema | Name | Result data type | Argument data types | Type | ++---+--+-++ | pg_catalog | xpath | xml[]| text, xml | normal | | pg_catalog | xpath | xml[]| text, xml, text[] | normal | | pg_catalog | xpath_exists | boolean | text, xml | normal | | pg_catalog | xpath_exists | boolean | text, xml, text[] | normal | | public | xpath_list| text | text, text | normal | | public | xpath_nodeset | text | text, text | normal | | public | xpath_nodeset | text | text, text, text| normal | ++---+--+-++ (7 rows) There is no xml-specific extension in /usr/local/pgsql/share/extensions. That dir. has the python and uuid stuff. -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Wed, Mar 14, 2012 at 8:24 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera vi...@khera.org wrote: I'll bet what happened was postgres re-wrote your table for you, effectively doing a compaction. You can get similar effect by doing an alter table and changing an INTEGER field to be INTEGER. Postgres does not optimize that do a no-op, so you get the re-writing effect. How does table rewriting work? Does it happen a row at a time or all at once? When you do something like alter type or update without a where clause, it will have to make a new copy of every old copy in the table. In other words, how much free disk space is needed on an 800 TB filesystem to rewrite a 550 TB table? (Have I got enough space?) If you update the whole table at once (a la alter table alter column or update with no where) then it has to have the space for all the real data to fit. The size of the file system isn't important as much as how much free space is left. IFF it's 800TB with exactly 550TB used, then you have 250TB free. The good news is that if the table is bloated, it should be able to just write to the free space in the table that's already there. This requires proper vacuuming and on older versions free space map settings. With a really large table, older versions of pgsql (=8.3) tended to blow out their free space map if you didn't crank them up. Newer versions just need proper regular vacuuming. -- 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] Did xpath_table get dropped.
Rob Sargent robjsarg...@gmail.com writes: I don't see anything to that effect in the release notes I've looked at. That's cause it's still there. Did you remember to build/install contrib/xml2? The xpath functions you're listing are all core, I think. 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
Executive summary: Why would the TOAST table on the slave have 4x the page count of the master? Is there a way to compact it if I don't have enough disk space to duplicate the table? How do I prevent this situation from recurring? On Wed, Mar 14, 2012 at 7:38 PM, Scott Marlowe scott.marl...@gmail.com wrote: The good news is that if the table is bloated, it should be able to just write to the free space in the table that's already there. Thank you, I got it. The table is not bloated, as per check_postgres.pl --action=bloat I compared number of pages on the large table between the Slony origin node and the slave, using SELECT relname, relpages FROM pg_class origin: 386,918 slave: 421,235 origin toast: 19,211,059 slave toast: 70,035,481 How about that? reltuples on origin toast table: 6.76368e+07 reltuples on slave toast table: 7.00354e+07 Here is my situation: 1. I've got an 800 GB (not TB as I wrote earlier today) filesystem. 2. 595 GB is in use by the Postgres database, and over 527 GB by this large table (including TOAST) 3. 155 GB is free. 4. pg_dump of this large table comes out to 212 GB in size (so physical size is nearly 3x the logical size) Why does the slave TOAST table have 4x the page count of the master? Best, Aleksey -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Wed, Mar 14, 2012 at 8:06 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: Executive summary: Why would the TOAST table on the slave have 4x the page count of the master? Hypothesis: the pages of the TOAST table on the slave have more unused space in them than the pages of the TOAST table on the master. How do I test this hypothesis? I need to examine the raw pages on disk. I'm looking at http://momjian.us/main/writings/pgsql/internalpics.pdf Slide 52 shows internal page structure but I don't know how to actually look at a page. Is it possible to do a raw dump of the pages of a table? Do I just look at the hexdump of the file corresponding to relfilenode? Best, Aleksey -- 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] copy in date string 00-00-00 00:00:00
On Mar 14, 2012, at 6:32 PM, Andy Colson wrote: On 03/14/2012 08:16 PM, Scott Marlowe wrote: Can you run it through sed and replace the -00-00 00:00:00 to NULL (no quotes) ? That should work. I think COPY (depending on arguments) uses \N by default. Another option is to pull it into a temp table and make fix it up from there. -Andy Thanks to all who responded. I appreciate it very much. Yes, that is a good idea. I wasn't sure how to use the NULL clause of the copy command, but I did wonder about other null values in the data set. There are many as this database grew up over 15+ years of use and many renditions of the client application. I am not familiar with sed, except for some trivial bits I nicked off the web. Enough to know it works, and to be dangerous. Nonetheless, using SED may be the way to go as there are two tables that contain a bit over 3,000,000 rows each. - Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] yum repository packages 9.0 and 9.1 libpq conflict
this is probably mostly for Devrim, since he's the packager of the RPM repository So I have a CentOS 6.2 x86_64 system that was running postgres 9.0 from Devrim's yum repo... I installed 9.1 and was going to do a parallel upgrade, but the entry for 9.0 in /etc/ld.so.conf.d/postgresql-9.0-libs.conf was causing /usr/pgsql-9.1/bin/psql to load the wrong libpq, /usr/pgsql-9.0/lib/libpq.so.5 which was triggering the error... psql: invalid connection option client_encoding I'm not sure how exactly this is supposed to work with two parallel versions installed. ld.so can't exactly sort out two libpq.so.5's .. I would have expected the 9.1 stuff to be -R path linked with the correct /usr/pgsql-9.1/lib stuff, and not to have ANY entries in /etc/ld.so.conf.d ... -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Wed, Mar 14, 2012 at 9:06 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: On Wed, Mar 14, 2012 at 7:38 PM, Scott Marlowe scott.marl...@gmail.com wrote: The good news is that if the table is bloated, it should be able to just write to the free space in the table that's already there. Thank you, I got it. The table is not bloated, as per check_postgres.pl --action=bloat Are you sure you're checking the toast table that goes with whatever parent table? Easy way to tell. du -s /var/lib/data/base dir, then update a few thousand rows, roll it back, and run du -s again. Compare. If the du numbers stay the same then you're updating pre-allocated space and should be ok. If there's a delta, compute it per tuple updated, multiply by tuples and that's how much you'll need. If the du -s numbers don't change or only a little then feel free to either run a single update while running watch df -h /var/lib/where/my/data/dir/lives and being ready to hit CTRL-C if you see if running your machine out of memory. -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Wed, Mar 14, 2012 at 10:57 PM, Scott Marlowe scott.marl...@gmail.com wrote: If the du -s numbers don't change or only a little then feel free to either run a single update while running watch df -h /var/lib/where/my/data/dir/lives and being ready to hit CTRL-C if you see if running your machine out of memory. OR batch them in batches of x 1 or so and df or du in between. -- 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] permission denied to create extension ltree Must be superuser to create this extension.
Ok, but can someone comment, document something on security of installing extensions for normal users? Does allowing access to extension provides a way to circumvent security model? If not why can't it be allowed for user installations (provided that extension was previously allowed in some conf file)? -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Wed, Mar 14, 2012 at 9:57 PM, Scott Marlowe scott.marl...@gmail.com wrote: Are you sure you're checking the toast table that goes with whatever parent table? Yep. I find out the relation id of the TOAST table: select reltoastrelid from pg_class where relname = 'parent_table_name'; Find out the relation name of the TOAST table: select X::regclass; (where X is what I got from step 1 above) Easy way to tell. du -s /var/lib/data/base dir, then update a few thousand rows, roll it back, and run du -s again. Compare. If the du numbers stay the same then you're updating pre-allocated space and should be ok. I don't think I can run this test while the system is in production... we do 250-300 database transactions per second under low load... high load takes us above 1500 tps. my numbers for du -sh data/base would be affected by the production workload. Let me ask you this: I've been looking at select ctid from big_table on the master and slave and I notice that pages can have holes in them. Some pages have rows that go sequentially from 0 to 26 or 27 or so, and some pages have rows that go: (431665,2) (431665,5) (431665,8) (431665,11) (431665,14) (431665,17) (431665,20) (431665,23) That's the last page on the slave. It has only 8 rows in it. It's composed mostly of holes. That aligns with my hypothesis that pages on the slave have less data in them than pages on the master. (Which would explain why the slave has 4x the number of pages compared to the master.) Is there any way to consolidate the pages on the slave without taking replication offline? Best, Aleksey -- 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] permission denied to create extension ltree Must be superuser to create this extension.
On Wed, Mar 14, 2012 at 9:28 PM, krz...@gmail.com krz...@gmail.com wrote: Ok, but can someone comment, document something on security of installing extensions for normal users? Does allowing access to extension provides a way to circumvent security model? If not why can't it be allowed for user installations (provided that extension was previously allowed in some conf file)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Extensions can be written in C, to me that seems like enough reason... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
