[GENERAL] Need help on how to backup a table
Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Victor -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16346381.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] table of US states' neighbours
On Thu, Mar 27, 2008 at 06:00:57PM -0400, Colin Wetherbee wrote:
brian wrote:
I'd like to add a table, state_neighbours, which joins each state with all
of its neighbour states. Does anyone know where I can find such a list?
I'm not interested in nearest neighbour, just any connected state.
That sounds like something you could create in 20 minutes with a map.
Or a few minutes with shapefiles and PostGIS, using the latter's
spatial functions to identify geometries that touch. Below are the
results of such an operation; I haven't verified the entire list
but I did check a few and they were correct.
AL|{FL,GA,MS,TN}
AR|{LA,MO,MS,OK,TN,TX}
AZ|{CA,CO,NM,NV,UT}
CA|{AZ,NV,OR}
CO|{AZ,KS,NE,NM,OK,UT,WY}
CT|{MA,NY,RI}
DC|{MD,VA}
DE|{MD,NJ,PA}
FL|{AL,GA}
GA|{AL,FL,NC,SC,TN}
IA|{IL,MN,MO,NE,SD,WI}
ID|{MT,NV,OR,UT,WA,WY}
IL|{IA,IN,KY,MI,MO,WI}
IN|{IL,KY,MI,OH}
KS|{CO,MO,NE,OK}
KY|{IL,IN,MO,OH,TN,VA,WV}
LA|{AR,MS,TX}
MA|{CT,NH,NY,RI,VT}
MD|{DC,DE,PA,VA,WV}
ME|{NH}
MI|{IL,IN,MN,OH,WI}
MN|{IA,MI,ND,SD,WI}
MO|{AR,IA,IL,KS,KY,NE,OK,TN}
MS|{AL,AR,LA,TN}
MT|{ID,ND,SD,WY}
NC|{GA,SC,TN,VA}
ND|{MN,MT,SD}
NE|{CO,IA,KS,MO,SD,WY}
NH|{MA,ME,VT}
NJ|{DE,NY,PA}
NM|{AZ,CO,OK,TX,UT}
NV|{AZ,CA,ID,OR,UT}
NY|{CT,MA,NJ,PA,VT}
OH|{IN,KY,MI,PA,WV}
OK|{AR,CO,KS,MO,NM,TX}
OR|{CA,ID,NV,WA}
PA|{DE,MD,NJ,NY,OH,WV}
RI|{CT,MA}
SC|{GA,NC}
SD|{IA,MN,MT,ND,NE,WY}
TN|{AL,AR,GA,KY,MO,MS,NC,VA}
TX|{AR,LA,NM,OK}
UT|{AZ,CO,ID,NM,NV,WY}
VA|{DC,KY,MD,NC,TN,WV}
VT|{MA,NH,NY}
WA|{ID,OR}
WI|{IA,IL,MI,MN}
WV|{KY,MD,OH,PA,VA}
WY|{CO,ID,MT,NE,SD,UT}
--
Michael Fuhr
--
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 on how to backup a table
On Thu, Mar 27, 2008 at 11:05 PM, ajcity [EMAIL PROTECTED] wrote: Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey
Re: [GENERAL] Need help on how to backup a table
CAJ CAJ wrote: Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey Thanks for quick response but pg_dump does not allow me to dump from a SQL SELECT query which is what I wanna do. -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16346814.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] dunction issue
I do not agree with you Sam.
Stored procedure are safe from hacking (from external access).
From my point of view transitions should be used only as internal purpose or
via intrAnet and not thru intErnet.
at list this is how under MS SQL they use to teach.
regarding unique constraint, i already setup it. :-)
unique violation will not help me in this case, or only to know if the email
is stored several time which i do not test...directly :-)
Al.
On Fri, Mar 28, 2008 at 1:19 AM, Sam Mason [EMAIL PROTECTED] wrote:
On Thu, Mar 27, 2008 at 03:34:49PM -0700, Adrian Klaver wrote:
Or a simpler way to do handle my previous suggestion:
IF (ret_email IS NULL ) OR (ret_email='') THEN
RETURN ('-3')
That would be the sane fix, yes.
Based on the previous emails from the OP, he seems to be missing a
lot of the tools that databases' give you. Transactions and unique
constraints being two significant ones. Writing stored procedures to do
their work is just going to introduce unnecessary bugs and complication.
Sam
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] Using tables in other PostGreSQL database
On Thu, Mar 27, 2008 at 10:29:37PM -0700, Swaminathan Saikumar wrote: 4. Why not provide that feature as a core feature, rather than an add-on? If the community really feels strongly about this, discourage this practice with a best-practices section, citing problems with examples, and workarounds. But why don't you provide this feature out of the box? After all, isn't widespread adoption of a high quality database like Postgres our overall goal? Why do people read the word add-on in a negative way? All it means is not installed by default, which is probably a good thing since the security implications are not trivial. Installation is just a question of: psql -f dblink install script (assuming your admin didn't do a minimal install). I'm unsure what widespread adoption of postgres has to do with any of this though. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Need help on how to backup a table
ajcity wrote: CAJ CAJ wrote: Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey Thanks for quick response but pg_dump does not allow me to dump from a SQL SELECT query which is what I wanna do. May be selective COPY will help you. http://www.postgresql.org/docs/8.3/interactive/sql-copy.html With regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.com ===
[GENERAL] Persistent user-defined functions
Hi all, I am using Ubuntu 7.10 with PostgreSQL 8.2. I have just finished creating two C functions and have successfully loaded them using CREATE OR REPLACE FUNCTION ... This was an extremely smooth process, and I have a lot of respect for the dev team for creating such a robust system. Anyway - I would like these new functions to be permanently available to a database, or to all databases. These functions will ultimately be called from PHP, where there is no guarantee of server state in between requests (maybe a reboot occurs and my functions are lost, etc...). On the other hand, it is inefficient to CREATE OR REPLACE all the time. Can anybody suggest a lightweight method to keep these functions permanently available, or point me towards a resource that might assist? Best regards, Dave. _ This Valentine's Day, get creative and show your sweetheart how much you care with flair! Find fun date ideas here! http://g.msn.ca/ca55/224 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Merge Joins and Views
Hello, I have a scenario with two tables, one with 5M rows and the other with about 3.7M (a subset taken from the first table). Each is clustered using its primary key (a single bigint column), and pg_stats shows that the id's correlation is 1 for both tables. In addition, I have a view over the 3.7M table that coalesces some columns that allow nulls. So I'm running a simple query that does a left outer join from the 5M to the 3.7M, which basically combines the information between the two (and results in 5M rows, of course). It seems to me that the best plan should involve two index scans and a merge join. However, I get different plans depending on whether I use the view or the underlying table directly, and even the use of ORDER BY -- see examples below for details. I don't know if this is a bug (I'm using version 8.3.0), but can anyone please explain why the optimizer (or rule system?) behaves this way? Thank you, --Chris -Example 1:- SELECT * FROM a LEFT OUTER JOIN b ON (a.id = b.id); Merge Left Join (cost=43.99..353657.21 rows=5001671 width=106) (actual time=0.653..32529.319 rows=500 loops=1) Merge Cond: (a.id = b.id) - Index Scan using a_pkey on a (cost=0.00..173752.86 rows=5001671 width=81) (actual time=0.353..9754.375 rows=500 loops=1) - Index Scan using b_pkey on b (cost=0.00..120980.85 rows=3713546 width=25) (actual time=0.279..8120.104 rows=3711523 loops=1) Total runtime: 33836.167 ms -Example 2:- -- v is a view that does SELECT ... FROM b; SELECT * FROM a LEFT OUTER JOIN v ON (a.id = v.id); Merge Left Join (cost=580217.86..822178.09 rows=5001671 width=100) (actual time=34260.004..67869.059 rows=500 loops=1) Merge Cond: (a.id = b.id) - Index Scan using a_pkey on a (cost=0.00..173752.86 rows=5001671 width=81) (actual time=0.270..10104.528 rows=500 loops=1) - Materialize (cost=580217.86..626637.18 rows=3713546 width=19) (actual time=34259.696..43199.389 rows=3711523 loops=1) - Sort (cost=580217.86..589501.72 rows=3713546 width=19) (actual time=34259.679..39448.310 rows=3711523 loops=1) Sort Key: b.id Sort Method: external sort Disk: 136632kB - Seq Scan on b (cost=0.00..61693.46 rows=3713546 width=25) (actual time=0.094..10224.402 rows=3711523 loops=1) Total runtime: 69202.529 ms -Example 3:- SELECT * FROM a LEFT OUTER JOIN ( SELECT * FROM v ORDER BY id ) sub ON (a.id = sub.id); Merge Right Join (cost=0.00..390792.67 rows=5001671 width=100) (actual time=0.497..38120.694 rows=500 loops=1) Merge Cond: (b.id = a.id) - Index Scan using b_pkey on b (cost=0.00..120980.85 rows=3713546 width=25) (actual time=0.262..13686.064 rows=3711523 loops=1) - Index Scan using a_pkey on a (cost=0.00..173752.86 rows=5001671 width=81) (actual time=0.219..11233.746 rows=500 loops=1) Total runtime: 39467.843 ms -- 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] Persistent user-defined functions
On Thu, Mar 27, 2008 at 11:10:39PM -0400, David T wrote: Anyway - I would like these new functions to be permanently available to a database, or to all databases. These functions will ultimately be called from PHP, where there is no guarantee of server state in between requests (maybe a reboot occurs and my functions are lost, etc...). On the other hand, it is inefficient to CREATE OR REPLACE all the time. Can anybody suggest a lightweight method to keep these functions permanently available, or point me towards a resource that might assist? Once you have created a function in a database, it stays until you drop it. No crash will lose it for example. You will have to do the creation once for each database and if you create them in template1 they will automatically appear whenever you create a new database. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] PL/pgSQL Documentation, biblio, etc
Hi guys, Is there any other online place, apart from http://www.postgresql.org/docs/8.3/static/plpgsql.html , to get a reference or a wider explanation of PL/pgSQL ? Do you recommend any book? thx Josep Porres
Re: [GENERAL] Need help on how to backup a table
ajcity wrote: Thanks all. The COPY command seems to do the work. One more thing, say I want the data dumped on a remote machine rather than on the current machine, how would I do that without having to first dump it on the local machine then uploading to the remote machine? Install psql client on that other m/c and fire command psql -c copy from there :) ofcourse with proper pg_hba.conf With regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.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/pgSQL Documentation, biblio, etc
On Friday 28. March 2008, josep porres wrote: Hi guys, Is there any other online place, apart from http://www.postgresql.org/docs/8.3/static/plpgsql.html , to get a reference or a wider explanation of PL/pgSQL ? Do you recommend any book? I found this page rather useful: http://www.onlamp.com/pub/a/onlamp/2006/05/11/postgresql-plpgsql.html hth, hand. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ -- 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 on how to backup a table
Thanks all. The COPY command seems to do the work. One more thing, say I want the data dumped on a remote machine rather than on the current machine, how would I do that without having to first dump it on the local machine then uploading to the remote machine? -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16347825.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/pgSQL Documentation, biblio, etc
am Fri, dem 28.03.2008, um 9:16:34 +0100 mailte josep porres folgendes: Hi guys, Is there any other online place, apart from http://www.postgresql.org/docs/8.3/ static/plpgsql.html , to get a reference or a wider explanation of PL/pgSQL ? A lot of well-explained code-snippets can you find here: http://varlena.com/varlena/GeneralBits/archive.php Unfortunately, no new entries for a long, long time... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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 on how to backup a table
If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? Thanks Victor -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16348299.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 on how to backup a table
am Fri, dem 28.03.2008, um 2:08:17 -0700 mailte ajcity folgendes: If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? Local file systems, and the user postgres needs write-access. I'm using /tmp/... for such. And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a table via 'create table as select ...' and COPY this table. And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? no matter Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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 on how to backup a table
ajcity wrote: If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? Thanks Victor from remote m/c psql -h some_hostname -c copy temp to stdout -Upostgres postgres temp.txt Will create temp file on the m/c from which u r firing the command . With regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.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] dunction issue
Alain Roger wrote:
I do not agree with you Sam.
Stored procedure are safe from hacking (from external access).
In that a stored procedure encapsulates a series of data operations,
meaning that the client doesn't have to know the details or even have
privileges to run the individual operations ? Yes, that can be really
useful, but it's hardly the full story.
Proper use of things like foreign keys, unique constraints, CHECK
constraints, etc adds another level of protection. I'd use those tools
before I restored to using a stored procedure. Like stored procedures,
users with appropriately limited priveleges are unable to bypass, drop,
or modify constraints.
That's true in any database with any sort of user privileges model.
For example, if you want to enforce the rule that a certain field must
have unique values in a table, do you think it's better to do it with a
stored procedure, or by adding a UNIQUE constraint to the field?
I'd say the UNIQUE constraint is better in every way. It's faster. It's
simple and unlike the stored procedure isn't at risk of being bypassed
by coding errors in the stored procedure. It's also self documenting, in
that the schema clearly shows the unque constraint rather than hiding it
in code. It's if anything more secure than a stored procedure because
it's simpler and can be easily protected against user modification.
The same goes for NOT NULL, CHECK constraints, foreign keys, etc.
You're also doing a lot of things in your stored procedure code the long
way. For example, instead of selecting a count() aggregate into a
variable and testing it, why not just use `EXISTS', or select the
information you really wanted and then use `IF FOUND' ?
For example:
-- It seems like not having an email address on record is an error.
-- Ensure that the problem is detected at INSERT/UPDATE time.
ALTER TABLE tmp_newsletterreg ALTER COLUMN email SET NOT NULL;
-- Really basic valiation of email addresses. It's not worth doing much
-- more than this sort of thing IMO because of performance issues and
-- transcient errors (MX lookup fail etc) when doing proper email
-- validation. At least now you don't have to revalidate in every
-- procedure.
ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check
CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]';
-- Add the same check on the user table. I imagine a NOT NULL
-- constraint there would also make sense.
ALTER TABLE users ADD CONSTRAINT simplistic_email_check
CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]';
-- Now, using those rules, redefine the stored procedure
CREATE OR REPLACE FUNCTION cust_portal.sp_u_002
(id_session character varying)
RETURNS character varying AS $BODY$
DECLARE
ret_email CHARACTER VARYING(512) :='';
BEGIN
set search_path = cust_portal;
-- Find the customer's email address, or NULL (and set NOT FOUND)
-- if no such customer exists.
SELECT email INTO ret_email FROM tmp_newsletterreg
WHERE tmp_usr_id = id_session;
IF FOUND THEN
IF NOT EXISTS (SELECT 1 FROM users
WHERE users.email= ret_email)
THEN
RETURN (ret_email);
ELSE
RETURN ('-2');
END IF;
ELSE
RETURN('-1');
END IF;
END;
$BODY$ LANGUAGE 'plpgsql';
Personally I think the use of text string error codes gets ugly fast.
I'd either rewrite the function to at least return an integer error code
as an OUT parameter:
CREATE OR REPLACE FUNCTION cust_portal.sp_u_002
(IN id_session character varying,
OUT ret_email character varying,
OUT err_code integer)
RETURNS record AS $BODY$
DECLARE
BEGIN
set search_path = cust_portal;
-- If we don't find the session, return -1 .
ret_email := NULL;
err_code := -1;
-- Find the customer's email address, or NULL (and set NOT FOUND)
-- if no such customer exists.
SELECT email INTO ret_email FROM tmp_newsletterreg
WHERE tmp_usr_id = id_session;
IF FOUND THEN
IF EXISTS (SELECT 1 FROM users
WHERE users.email= ret_email)
THEN
-- User already registered
ret_email := NULL;
err_code := '-2';
END IF;
END IF;
RETURN;
END;
$BODY$ LANGUAGE 'plpgsql';
[note: the above code hasn't actually been tested]
... or preferably throw informative exceptions. However, I do find it
frustrating that I can't attach a value or list of values to a
PostgreSQL exception in a way that is easy for the client app to extract
- I have to resort to text parsing (mega-ugly and unsafe) if I need to
do it.
Especially in an internationalised environment that's not nice.
Being able to obtain the exact exception name (as opposed to the full
error message), the full error string from an exception (without its
context) and also obtain the individual parameters substituted into an
exception string would be AMAZINGLY handy for use with JDBC etc.
From my point of
Re: [GENERAL] Need help on how to backup a table
Local file systems, and the user postgres needs write-access. I'm using /tmp/... for such. And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a table via 'create table as select ...' and COPY this table. I'm trying to avoid exporting to the local machine before uploading to the remote machine; I wanna just run it from the remote machine and have the data on the remote machine once its done. Is there a way to do this? -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16349003.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] dunction issue
On Fri, Mar 28, 2008 at 06:43:00PM +0900, Craig Ringer wrote: Alain Roger wrote: I do not agree with you Sam. Stored procedure are safe from hacking (from external access). In that a stored procedure encapsulates a series of data operations, meaning that the client doesn't have to know the details or even have privileges to run the individual operations ? Yes, that can be really useful, but it's hardly the full story. Indeed. And in my experience, it's the program's own developers you've got to be most cautious about. Hackers would have very little trouble breaking most software these days---almost everything is far too big and complicated, ignoring rules like keeping it simple, respecting the principle of least authority and other time tested rules. Attackers also tend to go around the barriers you put in their way, not through them, the most general attack would be the physical one, i.e. paying a cleaner to remove something important. Another way of looking at it is to witness the types of bugs being fixed in software, almost all of them have no security implications and are straight human fallibility. Proper use of things like foreign keys, unique constraints, CHECK constraints, etc adds another level of protection. I'd use those tools before I restored to using a stored procedure. Like stored procedures, users with appropriately limited priveleges are unable to bypass, drop, or modify constraints. Indeed, use the simplest possible tool to get the job done. And if possible reuse an existing one (i.e. all the work that has gone into getting the constraint handling working correctly in all the known cases). -- Really basic valiation of email addresses. It's not worth doing much -- more than this sort of thing IMO because of performance issues and -- transcient errors (MX lookup fail etc) when doing proper email -- validation. At least now you don't have to revalidate in every -- procedure. ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]'; Just out of interest, what's the lower() function call doing? I'd almost be tempted to do something like: CREATE DOMAIN emailaddr AS text CHECK (VALUE ~ '^[^ [EMAIL PROTECTED] ]+$'); and then use this instead of text/varchar types. ... or preferably throw informative exceptions. This would be my preference. It'll probably do the right thing if the code is called from other stored procedures then. However, I do find it frustrating that I can't attach a value or list of values to a PostgreSQL exception in a way that is easy for the client app to extract - I have to resort to text parsing (mega-ugly and unsafe) if I need to do it. Yup, why is this so often ignored when people write database drivers. I used the pyPgSQL python module (I think) for a bit, before realising that it even went as far as to helpfully automatically start a new transaction when the last one aborted. The resulting semantics meant my code did the most horrible things. And I'd agree with the remainder of your comments! Sam -- 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] Survey: renaming/removing script binaries (createdb, createuser...)
Greg Sabino Mullane napsal(a): snip Nobody want to rename psql. Personaly, I dislike current command names for long long time. Many times I tried create unix user by createuser command. And these names could be potential names of system commands. Yours is the first time I've heard of anyone with this problem. The useradd and adduser commands don't even start with the same letter. If it's that confusing, you can always use an alias or a symlink to make things more inline with what you want. It is not about letters but about memory :-). Currently it is not problem for me, but it was when I was starting play with Linux. For the record, I think any renaming is a terrible idea, and a solution in search of a problem. Any change, no matter how long it takes, will break untold number of scripts, make us look bad, and frustrate people, similar to the way that implicit cast removal did in 8.3, but without the Very Good Reason to show people why we made the change. I understand this point of view. And it is reason also why I asked if people use these commands or they prefer psql. For example nobody had complained that createtablespace command is missing. Does it mean that nobody uses tablespaces? It means everybody must use psql for tablespace creation. I personally use psql for everything. Only sometimes I use vacumdb or createuser command. Unfortunately, I not good survey maker and some tools usage statistic could be nice to have in survey as well. :( I have lived with current names and I can live with them in the future as well. Additionally, once we make the change, to which version do we refer to in the docs or when answering questions? You can't safely refer to the new commands until they've had time to percolate through as people update their database. And considering that I still work with some 7.3 system, and plenty of 7.4 ones, that could be a long time. Doc is related to version. And if you look on postmaster command in latest documentation that it says obsolete use postgres. *If* we're going to do this, at the very least it needs to be rolled out as a point revision update across all versions, so we minimize the confusion for people on older versions. We also need to keep symlinks or some other backwards-compatibilty around for a long time, *and* make a clean break at some future major version with lots of prior warning. I don't think so, that backport is necessary, but backward compatibility is obvious for new severals releases. By the way does postgreSQL has some EOL strategy? There are lot of OBSOLETE thinks mentioned in documentation, but I have never seen a list/roadmap when they will be removed. Zdenek -- 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 on how to backup a table
am Fri, dem 28.03.2008, um 3:01:43 -0700 mailte ajcity folgendes: Local file systems, and the user postgres needs write-access. I'm using /tmp/... for such. And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a table via 'create table as select ...' and COPY this table. I'm trying to avoid exporting to the local machine before uploading to the remote machine; I wanna just run it from the remote machine and have the data on the remote machine once its done. Is there a way to do this? Without quote-sign it's hard to understand, who has written what. Okay, you can mount the remote-filesystem over the network on the server, is this a option for you? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT DISTINCT ON and ORDER BY
Hello everybody, I have a table like this one: id value order_field 1 103 2 124 3 101 4 5 8 5 122 What I want to do, is to do something like SLECT DISTINCT ON (my_table.value) my_table.id, my_table.value, my_table.order_field FROM my_table ORDER BY order_field Hence selecting rows with distinct values, but primarily ordered by order_field, instead of value, which is requires by DISTINCT ON. The result in this case should be: id value order_field 3 101 5 122 4 5 8 How do I do this? I do need order_field in the select list to use it in the ORDER statement, which is why - as far as I can see - GROUP BY and SELECT DISTINCT are useless. Did I miss out on something? Thank you in advance
[GENERAL] Delete after trigger fixing the key of row numbers
Greetings from Finland to everyone! I joined the list to hit you with a question ;) I am developing an ERP to customer and I have made few tables using a row number as part of the key. Frex. Order rows table has a key of order number and row number like Receipt rows table has a key of Receipt number and row number. When deleting a line from such a table, I have made an after delete trigger, which fixes the row numbers with following command: UPDATE orderrow SET row = row - 1 WHERE order = old.order AND row old.row; Receiptrow table has a similiar trigger UPDATE receiptrow SET row = row - 1 WHERE receipt = old.receipt AND row old.row; My problem is that this command works fine on the orderrow table, but it gives an duplicate key violation error on the receipt table when there is at least two rows to be changed. It seems like it tries to do the change in wrong order at the receipt table. Frex. if I have 3 rows and I am deleting the first, it tries to change row number 3 to row number 2 first giving a duplicate error. I reindexed the receipt table with no help. I tried followin trigger with no help: UPDATE receiptrow SET row = row - 1 WHERE row in (SELECT row FROM receiptrow WHERE receipt = old.receipt AND row old.row ORDER BY row ASC); and I tried the order of DESC too. Does anyone have a glue what might be wrong? The keys of the tables are not so similar as in my example. Correct keys are Order row: 1. Shop code 2. Order number 3. Row number Receipt row: 1. Shop code 2. Date 3. Cash desktop code 4. Receipt number 5. Row number I am using PostgreSQL 8.2.5 at the moment. Best regards, Teemu Juntunen
Re: [GENERAL] SELECT DISTINCT ON and ORDER BY
On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote: id value order_field 1 10 3 2 12 4 3 10 1 45 8 5 12 2 Hence selecting rows with distinct values, but primarily ordered by order_field, instead of value, which is requires by DISTINCT ON. The result in this case should be: id value order_field 3 10 1 5 12 2 45 8 How do I do this? I do need order_field in the select list to use it in the ORDER statement, which is why - as far as I can see - GROUP BY and SELECT DISTINCT are useless. Did I miss out on something? ORDER BY's in conjunction with DISTINCT ON are used to specify which values you want for the other expressions in your query. For example for value 10, do you want id to be 1 or 2, and should the order be from the same row, or something else. You're additionally wanting to order by the order column, which you need to express as another step, i.e. a subselect something like: SELECT id, value FROM ( SELECT DISTINCT ON (value) id, value, order FROM table ORDER BY value, id) x ORDER BY order; No programming language will ever do exactly what you want straight away, it's a matter of using the tools it gives you. Sam -- 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] SELECT DISTINCT ON and ORDER BY
On Fri, 28 Mar 2008, Sam Mason [EMAIL PROTECTED] writes: On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote: The result in this case should be: id value order_field 3 10 1 5 12 2 45 8 SELECT id, value FROM ( SELECT DISTINCT ON (value) id, value, order FROM table ORDER BY value, id) x ORDER BY order; returns id | value +--- 1 |10 2 |12 4 | 5 to get the right results, append a DESC after id column in ORDER BY: id | value +--- 3 |10 5 |12 4 | 5 BTW, if I'm not mistaken, this solution assumes an order relation between your id and value columns. Regards. -- 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] SELECT DISTINCT ON and ORDER BY
maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 2008/3/28, Stanislav Raskin [EMAIL PROTECTED]: Hello everybody, I have a table like this one: id value order_field 1 103 2 124 3 101 4 5 8 5 122 What I want to do, is to do something like SLECT DISTINCT ON (my_table.value) my_table.id, my_table.value, my_table.order_field FROM my_table ORDER BY order_field Hence selecting rows with distinct values, but primarily ordered by order_field, instead of value, which is requires by DISTINCT ON. The result in this case should be: id value order_field 3 101 5 122 4 5 8 How do I do this? I do need order_field in the select list to use it in the ORDER statement, which is why – as far as I can see – GROUP BY and SELECT DISTINCT are useless. Did I miss out on something? Thank you in advance
Re: [GENERAL] Delete after trigger fixing the key of row numbers
Teemu Juntunen, e-ngine wrote: Greetings from Finland to everyone! On behalf of everyone, hello Finland. I joined the list to hit you with a question ;) That's what it's there for. I am developing an ERP to customer and I have made few tables using a row number as part of the key. Frex. Order rows table has a key of order number and row number like Receipt rows table has a key of Receipt number and row number. OK UPDATE receiptrow SET row = row - 1 WHERE receipt = old.receipt AND row old.row; My problem is that this command works fine on the orderrow table, but it gives an duplicate key violation error on the receipt table when there is at least two rows to be changed. It seems like it tries to do the change in wrong order at the receipt table. Known problem, I'm afraid. It's because the unique constraint is enforced by a unique index and that doesn't allow the test to be deferred until the end of the command, so processing order matters. There are three work-arounds: 1. Use -ve numbers as a temporary stage, to avoid the overlap. UPDATE rr SET row = - (row - 1) WHERE ... UPDATE rr SET row = - row WHERE row 0 2. Write your trigger using a loop that goes through renumbering one at a time, in order. 3. Have an AFTER UPDATE trigger as well as AFTER DELETE AFTER DELETE: UPDATE rr SET row=row-1 WHERE ... AND row = (old.row + 1) AFTER UPDATE: IF NEW.row = (OLD.row - 1) THEN UPDATE rr SET row=row-1 WHERE ... AND row = (old.row + 1) This one ripples through, renumbering. That any help? -- Richard Huxton Archonet Ltd -- 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] SELECT DISTINCT ON and ORDER BY
On Fri, 28 Mar 2008, Sam Mason [EMAIL PROTECTED] writes: On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote: The result in this case should be: id value order_field 3 10 1 5 12 2 45 8 Yet another lame solution: test=# SELECT max(id) AS id, min(value) AS value, min(weight) AS weight FROM tmp GROUP BY value ORDER BY min(weight); id | value | weight +---+ 3 |10 | 1 5 |12 | 2 4 | 5 | 8 (3 rows) Regards. -- 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 on how to backup a table
Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Here's an easy solution: psql -c COPY command here dbname | ssh [EMAIL PROTECTED] dd of=/path/tbl.backup You can run this from your local server, and immediately pipe it over a secure shell to the other server, and write it to a file there. The /path/ you specify is local to the remote server. -- 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] Survey: renaming/removing script binaries (createdb, createuser...)
Adam Rich wrote: Oh, then there should have been some options in the survey along the lines of things are fine how they are. Oh, a bit of answer-forcing wasn't beneath him. Ummm... Isn't that what Option A is about ? 1) What type of names do you prefer? --- a) old notation - createdb, createuser ... b) new one with pg_ prefix - pg_createdb, pg_creteuser ... c) new one with pg prefix - pgcreatedb, pgcreateuser ... d) remove them - psql is the solution e) remove them - pgadmin is the solution One very minimal idea that isn't listed here is just to rename createuser to createdbuser or createpguser, with similar changes for dropuser, createlang, and droplang. That gives all commands a 'db' or 'pg' in part of the command, with no underscores, and it matches the existing command that already have 'db'. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Survey: renaming/removing script binaries (createdb, createuser...)
On 2008-03-28 02:00, Andrej Ricnik-Bay wrote: On 28/03/2008, Dawid Kuroczko [EMAIL PROTECTED] wrote: Agree, except I would prefer pg instead of pgc. And it's been taken for about 35 years by a Unix command called page. From its man-page. pg - browse pagewise through text files So maybe pctl, consistent with psql. It is short enough, does not need shift and does not confuse, if man knows that it has something to do with Postgres. It looks it is yet not taken. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Users, groups and inheritance questions
Hi chaps, Apologies in advance if there's something in the docs I've missed here, but I have had a good look around and I can't find a good explanation anywhere. I'm looking at setting up group roles to manage our users, but I can't quite get my head around how the inheritance is supposed to work, I'm hoping I've just totally overlooked something here. I created a group 'admins' as follows: CREATE ROLE admins NOSUPERUSER NOINHERIT CREATEDB CREATEROLE; Then I create a user in this group: CREATE USER test WITH PASSWORD 'passw' IN GROUP admins; So I can see in pgAdmin for my test user: CREATE ROLE test LOGIN ENCRYPTED PASSWORD 'md5b140e5c3c4fb663063316e011e54ec3d' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT admins TO test; This test user can't create databases, nor can it create roles. I get permission denied to create role I thought that if user 'test' was in group 'admins' and I specified INHERIT then it'd inherit those permissions? I'm confused?? Thanks Glyn __ Sent from Yahoo! Mail. A Smarter Inbox http://uk.docs.yahoo.com/nowyoucan.html -- 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] Merge Joins and Views
Chris Mayfield [EMAIL PROTECTED] writes: [ planner finds better plan with a forced ORDER BY ] That shouldn't happen. Can you show the details of your case? It may be something specific to the particular view definition... 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] creating a trigger to access another postgres database?
I have a question regarding postgres Trigger. We have two applications which connect to two different databases (Both are postgres). Is it possible to create a trigger (row based) in one database say A, which can access another database say B and updates a table there. In brief, Is it possible to create Trigger in one database, which can connect to another database for making an update in the other database table. thanks, - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
Re: [GENERAL] table of US states' neighbours
Michael Fuhr wrote:
On Thu, Mar 27, 2008 at 06:00:57PM -0400, Colin Wetherbee wrote:
brian wrote:
I'd like to add a table, state_neighbours, which joins each state with all
of its neighbour states. Does anyone know where I can find such a list?
I'm not interested in nearest neighbour, just any connected state.
That sounds like something you could create in 20 minutes with a map.
Or a few minutes with shapefiles and PostGIS, using the latter's
spatial functions to identify geometries that touch. Below are the
results of such an operation; I haven't verified the entire list
but I did check a few and they were correct.
Brilliant, thanks. Yes the PostGIS angle is why i asked on this list. I
figured that this info must be readily available. Now google will pick
it up for the next bloke.
AL|{FL,GA,MS,TN}
AR|{LA,MO,MS,OK,TN,TX}
AZ|{CA,CO,NM,NV,UT}
CA|{AZ,NV,OR}
CO|{AZ,KS,NE,NM,OK,UT,WY}
CT|{MA,NY,RI}
DC|{MD,VA}
DE|{MD,NJ,PA}
FL|{AL,GA}
GA|{AL,FL,NC,SC,TN}
IA|{IL,MN,MO,NE,SD,WI}
ID|{MT,NV,OR,UT,WA,WY}
IL|{IA,IN,KY,MI,MO,WI}
IN|{IL,KY,MI,OH}
KS|{CO,MO,NE,OK}
KY|{IL,IN,MO,OH,TN,VA,WV}
LA|{AR,MS,TX}
MA|{CT,NH,NY,RI,VT}
MD|{DC,DE,PA,VA,WV}
ME|{NH}
MI|{IL,IN,MN,OH,WI}
MN|{IA,MI,ND,SD,WI}
MO|{AR,IA,IL,KS,KY,NE,OK,TN}
MS|{AL,AR,LA,TN}
MT|{ID,ND,SD,WY}
NC|{GA,SC,TN,VA}
ND|{MN,MT,SD}
NE|{CO,IA,KS,MO,SD,WY}
NH|{MA,ME,VT}
NJ|{DE,NY,PA}
NM|{AZ,CO,OK,TX,UT}
NV|{AZ,CA,ID,OR,UT}
NY|{CT,MA,NJ,PA,VT}
OH|{IN,KY,MI,PA,WV}
OK|{AR,CO,KS,MO,NM,TX}
OR|{CA,ID,NV,WA}
PA|{DE,MD,NJ,NY,OH,WV}
RI|{CT,MA}
SC|{GA,NC}
SD|{IA,MN,MT,ND,NE,WY}
TN|{AL,AR,GA,KY,MO,MS,NC,VA}
TX|{AR,LA,NM,OK}
UT|{AZ,CO,ID,NM,NV,WY}
VA|{DC,KY,MD,NC,TN,WV}
VT|{MA,NH,NY}
WA|{ID,OR}
WI|{IA,IL,MI,MN}
WV|{KY,MD,OH,PA,VA}
WY|{CO,ID,MT,NE,SD,UT}
--
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] Users, groups and inheritance questions
Glyn Astill [EMAIL PROTECTED] writes: I thought that if user 'test' was in group 'admins' and I specified INHERIT then it'd inherit those permissions? No, inheritance of permissions only works for GRANT-able permissions; the special role properties like CREATEDB are outside that scope. I think though that if test does SET ROLE admins then she'd be able to create a database. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
On Fri, Mar 28, 2008 at 3:41 PM, Tomasz Ostrowski [EMAIL PROTECTED] wrote: On 2008-03-28 02:00, Andrej Ricnik-Bay wrote: On 28/03/2008, Dawid Kuroczko [EMAIL PROTECTED] wrote: Agree, except I would prefer pg instead of pgc. And it's been taken for about 35 years by a Unix command called page. From its man-page. pg - browse pagewise through text files So maybe pctl, consistent with psql. It is short enough, does not need shift and does not confuse, if man knows that it has something to do with Postgres. It looks it is yet not taken. I like it. Personally pctl feels better than pgc :-) Regards, Dawid -- 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] Users, groups and inheritance questions
Hello Glyn,its confusing, but You didnt read the manual very carefully!Short excerpt:The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges fordatabase objects and role memberships). It does not apply to the special role attributes set by CREATEROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does notimmediately grant the ability to create databases, even if INHERIT is set; it would be necessary tobecome that role via SET ROLE before creating a database.bye...Ludwig
Re: [GENERAL] Persistent user-defined functions
if you create its in a template1 regards... El jue, 27-03-2008 a las 23:10 -0400, David T escribió: Hi all, I am using Ubuntu 7.10 with PostgreSQL 8.2. I have just finished creating two C functions and have successfully loaded them using CREATE OR REPLACE FUNCTION ... This was an extremely smooth process, and I have a lot of respect for the dev team for creating such a robust system. Anyway - I would like these new functions to be permanently available to a database, or to all databases. These functions will ultimately be called from PHP, where there is no guarantee of server state in between requests (maybe a reboot occurs and my functions are lost, etc...). On the other hand, it is inefficient to CREATE OR REPLACE all the time. Can anybody suggest a lightweight method to keep these functions permanently available, or point me towards a resource that might assist? Best regards, Dave. _ This Valentine's Day, get creative and show your sweetheart how much you care with flair! Find fun date ideas here! http://g.msn.ca/ca55/224 -- 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] Using tables in other PostGreSQL database
On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar [EMAIL PROTECTED] wrote: I am fairly new to Postgres. However, I have to say that I agree with Barry's comments. The real problem here is that you are not using the db properly. You should have one db with all these data in it in different schemas. PostgreSQL provides you with the ability to segregate these data via schemas and fine grained (by the table) ACLs. Your refusal to use multiple schemas in one database due to some perceived problem with them all being in the same database is what's causing your issues. Put your data into various schemas in one database and you can then use access control to decide who sees what. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database trigger across multiple postgres databases
I have a question regarding postgres Trigger. We have two applications which connect to two different databases (Both are postgres). Is it possible to create a trigger (row based) in one database say A, which can access another database say B and updates a table there. In brief, Is it possible to create Trigger in one database, which can connect to another database for making an update in the other database table. thanks, - Never miss a thing. Make Yahoo your homepage.
[GENERAL] PostgreSQL terminates after crash of another server process
Hello, I have a trouble with PG and can't find out why it terminates and goes to recovery mode :( LOG: 2008-03-28 13:29:39 LOG: server process (PID 6852) exited with exit code 3 2008-03-28 13:29:39 LOG: terminating any other active server processes ... 2008-03-26 17:29:39 FATAL: the database system is in recovery mode SYSTEM: Windows Server 2003 x64 R2 4 Gb RAM PostgreSQL 8.2.5 -- View this message in context: http://www.nabble.com/PostgreSQL-terminates-after-crash-of-another-server-process-tp16349328p16349328.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] Delete after trigger fixing the key of row numbers
Greetings from Finland to everyone! I joined the list to hit you with a question ;) I am developing an ERP to customer and I have made few tables using a row number as part of the key. Frex. Order rows table has a key of order number and row number like Receipt rows table has a key of Receipt number and row number. When deleting a line from such a table, I have made an after delete trigger, which fixes the row numbers with following command: UPDATE orderrow SET row = row - 1 WHERE order = old.order AND row old.row; Receiptrow table has a similiar trigger UPDATE receiptrow SET row = row - 1 WHERE receipt = old.receipt AND row old.row; My problem is that this command works fine on the orderrow table, but it gives an duplicate key violation error on the receipt table when there is at least two rows to be changed. It seems like it tries to do the change in wrong order at the receipt table. Frex. if I have 3 rows and I am deleting the first, it tries to change row number 3 to row number 2 first giving a duplicate error. I reindexed the receipt table with no help. I tried followin trigger with no help: UPDATE receiptrow SET row = row - 1 WHERE row in (SELECT row FROM receiptrow WHERE receipt = old.receipt AND row old.row ORDER BY row ASC); and I tried the order of DESC too. Does anyone have a glue what might be wrong? The keys of the tables are not so similar as in my example. Correct keys are Order row: 1. Shop code 2. Order number 3. Row number Receipt row: 1. Shop code 2. Date 3. Cash desktop code 4. Receipt number 5. Row number I am using PostgreSQL 8.2.5 at the moment. Best regards, Teemu Juntunen
Re: [GENERAL] creating a trigger to access another postgres database?
carty mc wrote: I have a question regarding postgres Trigger. We have two applications which connect to two different databases (Both are postgres). Is it possible to create a trigger (row based) in one database say A, which can access another database say B and updates a table there. Sure - check the manuals (in 8.3) for dblink - it's in contrib. It's been there for a while, but the documentation for contrib modules wasn't in the manual until 8.3 -- Richard Huxton Archonet Ltd -- 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 on how to backup a table
ashish-21 wrote: ajcity wrote: If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? Thanks Victor from remote m/c psql -h some_hostname -c copy temp to stdout -Upostgres postgres temp.txt Will create temp file on the m/c from which u r firing the command . With regards Ashish Thanks all I tried this option and it worked PERFECTLY. Thanks every body. Victor -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16352647.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] SELECT DISTINCT ON and ORDER BY
Yes, it works fine. Never came to my mind to simply use aggregate functions on fields which I do not want in the group clause. Is it common practice to do so in such cases? It seems odd somehow. _ Von: josep porres [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 28. März 2008 14:15 An: Stanislav Raskin Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] SELECT DISTINCT ON and ORDER BY maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 2008/3/28, Stanislav Raskin [EMAIL PROTECTED]: Hello everybody, I have a table like this one: id value order_field 1 103 2 124 3 101 4 5 8 5 122 What I want to do, is to do something like SLECT DISTINCT ON (my_table.value) my_table.id, my_table.value, my_table.order_field FROM my_table ORDER BY order_field Hence selecting rows with distinct values, but primarily ordered by order_field, instead of value, which is requires by DISTINCT ON. The result in this case should be: id value order_field 3 101 5 122 4 5 8 How do I do this? I do need order_field in the select list to use it in the ORDER statement, which is why as far as I can see GROUP BY and SELECT DISTINCT are useless. Did I miss out on something? Thank you in advance
[GENERAL] Schema design question
I'm working on a project which requires me to keep track of objects, each of which can have an arbitrary number of attributes. Although there will be many attributes that an object can have, the data types of those attributes won't be all that varried (int, float, text, boolean, date, etc.). My question is: what tradeoffs have you seen in picking a solution to this problem? In other words: create table attrs (id serial primary key, name text); create table obj (id serial primary key, name text); create table att (oid int references obj.id, aid int references attrs.id, value text); (everything smashed down to text by the application) versus: create table attrs (id serial primary key, name text); create table obj (id serial primary key, name text); create table att_int (oid int references obj.id, aid int references attrs.id, value int); create table att_float (oid int references obj.id, aid int references attrs.id, value float); create table att_text (oid int references obj.id, aid int references attrs.id, value text); create table att_bool (oid int references obj.id, aid int references attrs.id, value boolean); create table att_date (oid int references obj.id, aid int references attrs.id, value date); (everything kept distinct, probably with the application using stored procs) or even versus: create table attrs (id serial primary key, name text); create table obj (id serial primary key, name text); create table att (oid int references obj.id, aid int references attrs.id, value_int int, value_float float, value_text text, value_bool bool, value_date date); (the worst or the best of both worlds?) The first certainly seems simplest, while the second seems like it offers much better ability for constraint checking and probably more compact storage. The second also seems like a perfect example for inheritence, except that I don't believe inheritence allows for some usuful things like uniqueness, so if I wanted to make my primary key in the att_* tables (oid,aid), then I dont' think I could use inheritence. The third option doesn't seem all that different than the second option to me, though maybe it has benefits I'm not seeing. Anyway, this seems like a common problem without a perfect solution, and I'm sure people must have hindsight opinions on how they solved it. Your thoughts? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table of US states' neighbours
On Thu, Mar 27, 2008 at 11:33 PM, Michael Fuhr [EMAIL PROTECTED] wrote:
On Thu, Mar 27, 2008 at 06:00:57PM -0400, Colin Wetherbee wrote:
brian wrote:
Or a few minutes with shapefiles and PostGIS, using the latter's
spatial functions to identify geometries that touch. Below are the
results of such an operation; I haven't verified the entire list
but I did check a few and they were correct.
AL|{FL,GA,MS,TN}
AR|{LA,MO,MS,OK,TN,TX}
AZ|{CA,CO,NM,NV,UT}
Where is Hawaii and Alaska? :o)
--
Regards,
Richard Broersma Jr.
--
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_stat_user_tables
I'm selecting tables from my db using query: SELECT pg_stat_user_tables.relname FROM pg_stat_user_tables WHERE (pg_stat_user_tables.relname LIKE 'name_hosp_%') The problem I have is that the table names returned by this query are different from the ones in my db. Not all of them but 10%-20%. The common problem I noticed is adding _ at the end of table name and truncating name hospital to hosp. Anyone ever had similar problem? Any other way to get list of table names? Thanks, JP -- 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] Users, groups and inheritance questions
Thanks Tom ludwig, I understand now. ba rel=nofollowGlyn Astill/a /b - Original Message From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, 28 March, 2008 3:24:34 PM Subject: Re: [GENERAL] Users, groups and inheritance questions Hello Glyn, it's confusing, but You didn't read the manual very carefully! Short excerpt: The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for database objects and role memberships). It does not apply to the special role attributes set by CREATE ROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does not immediately grant the ability to create databases, even if INHERIT is set; it would be necessary to become that role via SET ROLE before creating a database. bye... Ludwig __ Sent from Yahoo! Mail. A Smarter Inbox http://uk.docs.yahoo.com/nowyoucan.html
Re: [GENERAL] Delete after trigger fixing the key of row numbers
On 2008-03-28 13:27, Teemu Juntunen wrote: I am developing an ERP to customer and I have made few tables using a row number as part of the key. When deleting a line from such a table, I have made an after delete trigger, which fixes the row numbers with following command: UPDATE orderrow SET row = row - 1 WHERE order = old.order AND row old.row; Whoa! That was a very bad design decision. This will eat your data sooner or later. It seems like it tries to do the change in wrong order at the receipt table. You can force an order of updates using a loop in plpgsql. But you should redesign your database before it is too late. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table of US states' neighbours
Richard Broersma wrote:
On Thu, Mar 27, 2008 at 11:33 PM, Michael Fuhr [EMAIL PROTECTED] wrote:
Or a few minutes with shapefiles and PostGIS, using the latter's
spatial functions to identify geometries that touch. Below are the
results of such an operation; I haven't verified the entire list
but I did check a few and they were correct.
AL|{FL,GA,MS,TN}
AR|{LA,MO,MS,OK,TN,TX}
AZ|{CA,CO,NM,NV,UT}
Where is Hawaii and Alaska? :o)
The OP said he didn't want HI and AK in the list since they don't
touch other states.
Colin
--
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] Merge Joins and Views
See attached -- I've simplified my actual database quite a bit, but this
example shows the same results.
Thanks,
--Chris
--
-- Why does the optimizer insist on sorting a clustered table?
--
-- NOTE: This script requires 540 MB of disk space and about
-- 12 minutes to run (on my good old Sun-Blade-1000, at least).
--
SELECT version();
DROP VIEW IF EXISTS v;
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
CREATE TABLE a (
id integer PRIMARY KEY,
val float8 DEFAULT random()
);
CREATE TABLE b (
id integer PRIMARY KEY,
opt float8 DEFAULT CASE WHEN random() .5 THEN random() END
);
CREATE VIEW v AS
SELECT id, COALESCE(opt, 0) AS opt FROM b;
-
INSERT INTO a
SELECT * FROM generate_series(1, 500);
INSERT INTO b
SELECT * FROM generate_series(1, 3711523);
ANALYZE a;
ANALYZE b;
-- note the correlation for the id columns is 1
SELECT * FROM pg_stats
WHERE tablename IN ('a', 'b');
-
-- Example 1: left merge join over two index scans
EXPLAIN ANALYZE
SELECT * FROM a LEFT OUTER JOIN b ON (a.id = b.id);
-- Example 2: left merge join over an index scan and seqscan + sort
EXPLAIN ANALYZE
SELECT * FROM a LEFT OUTER JOIN v ON (a.id = v.id);
-- Example 3: tricks the optimizer into a right merge join
EXPLAIN ANALYZE
SELECT * FROM a LEFT OUTER JOIN (
SELECT * FROM v ORDER BY id
) sub ON (a.id = sub.id);
version
--
PostgreSQL 8.3.0 on sparc-sun-solaris2.8, compiled by GCC 2.95.2
(1 row)
DROP VIEW
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE VIEW
INSERT 0 500
INSERT 0 3711523
ANALYZE
ANALYZE
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs |
histogram_bounds
| correlation
+---+-+---+---++--+---++-
public | a | id | 0 | 4 | -1 |
| |
{226,480817,946403,1463901,1905168,2486162,2964834,3411486,3947522,4446167,4996780}
| 1
public | a | val | 0 | 8 | -1 |
| |
{0.00023875804618001,0.091457224917,0.189253146760166,0.282982839271426,0.393971057608724,0.491479988675565,0.592469296883792,0.693580291699618,0.803486418910325,0.899317930918187,0.49590768665}
| -0.0345742
public | b | id | 0 | 4 | -1 |
| |
{2380,409226,804058,1186283,1525765,1874817,2199262,2566896,2939230,3316455,3709638}
| 1
public | b | opt | 0.503667 | 8 | -1 |
| |
{0.000438648741692305,0.0946335387416184,0.194745551329106,0.308890894055367,0.403113955631852,0.50895657017827,0.62006954383105,0.724281970411539,0.805469979997724,0.907830006908625,0.40330628306}
|0.034033
(4 rows)
QUERY PLAN
--
Merge Left Join (cost=0.00..330371.44 rows=5000180 width=24) (actual
time=0.319..30850.276 rows=500 loops=1)
Merge Cond: (a.id = b.id)
- Index Scan using a_pkey on a (cost=0.00..156882.50 rows=5000180
width=12) (actual time=0.244..12665.648 rows=500 loops=1)
- Index Scan using b_pkey on b (cost=0.00..114600.84 rows=3711012
width=12) (actual time=0.061..7336.846 rows=3711523 loops=1)
Total runtime: 32191.735 ms
(5 rows)
QUERY PLAN
--
Merge Left Join (cost=560793.89..785842.02 rows=5000180 width=24) (actual
time=23542.157..55124.203 rows=500 loops=1)
Merge Cond: (a.id = b.id)
- Index Scan using a_pkey on a (cost=0.00..156882.50 rows=5000180
width=12)
Re: [GENERAL] PL/pgSQL Documentation, biblio, etc
Hello it's on czech site, but in english lang http://www.pgsql.cz/index.php/Introduction_to_PostgreSQL_SQL http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29 http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks Regards Pavel Stehule On 28/03/2008, josep porres [EMAIL PROTECTED] wrote: Hi guys, Is there any other online place, apart from http://www.postgresql.org/docs/8.3/static/plpgsql.html , to get a reference or a wider explanation of PL/pgSQL ? Do you recommend any book? thx Josep Porres -- 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] Schema design question
Ben wrote: create table attrs (id serial primary key, name text); create table obj (id serial primary key, name text); create table att (oid int references obj.id, aid int references attrs.id, value_int int, value_float float, value_text text, value_bool bool, value_date date); I think I saw mention here of the DB using a bitmap in the tuple header to avoid storing NULL fields. If that's the case (don't trust my word on it), then combined with a CHECK constraint that ensures that at most one of your typed fields may be not null, this option might at least prove to be the most efficient. However, it won't be fun to query. Storing them all as text won't be much fun to query, which I'd consider another argument for the many-types tuple. '2' '11' = 't', '002' '2', etc. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
VS: [GENERAL] Delete after trigger fixing the key of row numbers
Hi Tomasz, with receipt rows I can't see any other option with key as running number. First I tried to use serial, but it didn't start from zero again when the foreign key (receipt number) changes. It just continues the serial despite of the foreign key. Then I decided to do my own serial which keeps it in order even when some rows are deleted from the middle. Of course I could just leave the row numbers as is, but there are some advantages at the program, if the serial is linear. What is the danger you see in this row number update trigger? Richard, thank you for your great answer with many solutions! Best regards, Teemu Juntunen -Alkuperäinen viesti- Lähettäjä: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Puolesta Tomasz Ostrowski Lähetetty: 28. maaliskuuta 2008 18:20 Vastaanottaja: Teemu Juntunen Kopio: pgsql-general@postgresql.org Aihe: Re: [GENERAL] Delete after trigger fixing the key of row numbers On 2008-03-28 13:27, Teemu Juntunen wrote: I am developing an ERP to customer and I have made few tables using a row number as part of the key. When deleting a line from such a table, I have made an after delete trigger, which fixes the row numbers with following command: UPDATE orderrow SET row = row - 1 WHERE order = old.order AND row old.row; Whoa! That was a very bad design decision. This will eat your data sooner or later. It seems like it tries to do the change in wrong order at the receipt table. You can force an order of updates using a loop in plpgsql. But you should redesign your database before it is too late. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] trouble selecting from array
On Thu, Mar 27, 2008 at 8:58 PM, Tom Lane [EMAIL PROTECTED] wrote: If you want a sub-array you need to use the slice notation, eg tdr_tags[2:2][1:2] The slice approach is not a general solution...in fact there seems to be no way to convert an array of N dimensions to N-1 dimensions except in the special case of N=1. One side effect of this is that the _pg_expand_array approaches which I was about to suggest to the OP only work for one dimensional arrays. reading the archives, you wrote: Because it isn't a slice expression --- you used colon nowhere, so the result type is going to be text not text[]. (Remember that the parser must determine the expression's result type at parse time, so whether there are enough subscripts can't enter into this.) Our alternatives here are to throw a subscripting error or return NULL. I'd personally have gone with throwing an error, I think, but it seems far too late to revisit that decision. is there not enough information available to the parser to reduce the expression dimensions by one? 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: VS: [GENERAL] Delete after trigger fixing the key of row numbers
Teemu Juntunen, e-ngine wrote: Hi Tomasz, with receipt rows I can't see any other option with key as running number. First I tried to use serial, but it didn't start from zero again when the foreign key (receipt number) changes. It just continues the serial despite of the foreign key. Then I decided to do my own serial which keeps it in order even when some rows are deleted from the middle. Of course I could just leave the row numbers as is, but there are some advantages at the program, if the serial is linear. What is the danger you see in this row number update trigger? If you'd like to have a linear count of your result set it shouldn't be difficult to implement that in your client-side application code (for loop, for instance). You don't need to use the serial for display purposes but it's very helpful for maintaining intact relations. Besides, if this table has any associations with others, doing this would seem to make your archived data next to useless if you ever need it. b -- 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] trouble selecting from array
On Fri, Mar 28, 2008 at 2:19 PM, Merlin Moncure [EMAIL PROTECTED] wrote: reading the archives, you wrote: Because it isn't a slice expression --- you used colon nowhere, so the result type is going to be text not text[]. (Remember that the parser must determine the expression's result type at parse time, so whether there are enough subscripts can't enter into this.) Our alternatives here are to throw a subscripting error or return NULL. I'd personally have gone with throwing an error, I think, but it seems far too late to revisit that decision. is there not enough information available to the parser to reduce the expression dimensions by one? ugh, I think I see the problem. You have some function that returns an int[], which does not have a defined number of dimensions, so: select func()[] is not known to return an int or an int[] at parse time, so the path of least resistance was to assume 'int'. sorry for the noise. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memory
I have a client that experienced several Out Of Memory errors a few weeks ago (March 10 11), and I'd like to figure out the cause. In the logs it's showing that they were getting out of memory errors for about 0.5-1 hour, after which one of the processes would crash and take the whole database down. After they restarted the server it would eventually start giving out of memory messages and crash again. This happened a total of five times over a 24 hour period. After that we did not see these errors again. They did upgrade to 8.1.11 on the 14th, and have also moved some of the databases to different servers afterwards. First some background information: Software (at the time of the memory errors): CentOS 4.5 (x86_64) running its 2.6.9-55.ELsmp Linux kernel, PostgreSQL 8.1.9 (from RPMs provided on the PostgreSQL web site: postgresql-8.1.9-1PGDG.x86_64). Hardware: 4 dual-core Opterons. 16GB physical RAM, 2GB swap. Database: they use persistent connections, and usually have around 1000 open database connections. The vast majority of those are usually idle. They do run a lot of queries though. The total size of the databases in this cluster is 36GB, with the largest database being 21GB, and the largest table being 2.5GB (having 20 million tuples). Highlights of postgresql.conf settings: max_connections = 2000 shared_buffers = 12 work_mem = 4096 maintenance_work_mem = 268435 max_fsm_pages = 150 max_fsm_relations = 2000 checkpoint_segments = 8 effective_cache_size = 140 stats_start_collector = on stats_command_string = off stats_block_level = on stats_row_level = on stats_reset_on_server_start = off autovacuum = on lc_* = 'en_US.UTF-8' Note that not all of these settings may have been in effect during the first crash since the server hadn't been restarted for quite some time. However, during the second and third crash these settings were in effect. ulimit settings: core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 137216 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Miscellaneous facts: Before the crashes there were a lot of duplicate key violates unique constraint ... errors as well as some syntax errors in some of the queries. A lot of these errors were fixed after the crashes. I don't think these would be related to the problem unless there's a memory leak somewhere. This server does not have any other processes running that could consume a significant amount of memory. Some of the other processes that run are SSH, cupsd, snmpd, sendmail, portsentry, xinetd, and Dell OpenManage. This server has no cronjobs. These errors usually seemed to occur right after autovacuum started vacuuming a database, although I'm not sure if autovacuum is the cause here. Here are some of the relevant portions from the logs (some of the database/relation names are obfuscated to protect confidentiality): 2008-03-10 18:42:58 EDT LOG: autovacuum: processing database database1 [this database is 4GB in size] TopMemoryContext: 401632 total in 11 blocks; 12832 free (12 chunks); 388800 used TopTransactionContext: 8192 total in 1 blocks; 7352 free (11 chunks); 840 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used Autovacuum context: 253952 total in 5 blocks; 131152 free (147 chunks); 122800 used smgr relation table: 24576 total in 2 blocks; 15968 free (4 chunks); 8608 used Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 24576 total in 2 blocks; 7632 free (3 chunks); 16944 used CacheMemoryContext: 2157568 total in 10 blocks; 941176 free (0 chunks); 1216392 used some_pkey: 1024 total in 1 blocks; 312 free (0 chunks); 712 used [note: this index is currently about 41MB but only has 129 small tuples - it's currently very bloated] pg_index_indrelid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_constraint_conrelid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_autovacuum_vacrelid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_database_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_type_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_trigger_tgrelid_tgname_index: 1024 total in
Re: [GENERAL] dunction issue
Sam Mason wrote: ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]'; Just out of interest, what's the lower() function call doing? Absolutely nothing. That's what I get for reading my mail at stupid-o-clock in the morning (Australia) instead of something sensible like sleeping. It's there because I was thinking about case insensitive domain comparison, but I couldn't begin to guess how it made its way into the constraint expression. I'd almost be tempted to do something like: CREATE DOMAIN emailaddr AS text CHECK (VALUE ~ '^[^ [EMAIL PROTECTED] ]+$'); and then use this instead of text/varchar types. I was thinking about something like that, but my own storage of email addresses actually splits them into user part and domain part (so it can handle the case sensitivity differently - user parts are may be case sensitive depending on the mail system so you can't assume they're the same if they only differ in case; domain parts are never case sensitive) and that would've unnecessarily complicated the example. I didn't think to go for the half way point. ... or preferably throw informative exceptions. This would be my preference. It'll probably do the right thing if the code is called from other stored procedures then. Yep, it's what I'll do in almost all cases. I often land up writing client / UI data validation code to perform the same checks and catch the issue before submitting anything to the DB, but I don't consider this unreasonable. The DB's checks are protecting data integrity and consistency; the UI's checks are improving the user/app interaction by providing early (and usually more friendly) notification of data issues. They're really quite different jobs. Occasionally, though, I do have something where the DB-using app must just submit a request to the DB and see if it works. Either the UI doesn't have the privileges to run the same checks its self, or they're just too expensive to do from the client (or to do twice). In those cases I start to find Pg's error reporting frustrating, and I either resort to a return value sort of approach or embed a unique error code and some parseable values in the exception string. Eg: Some kind of human-readable error description goes here [ERR114:ID=12200;CONFLICTING-ID=] It's not pretty, but it works. Yup, why is this so often ignored when people write database drivers. I used the pyPgSQL python module (I think) for a bit, before realising that it even went as far as to helpfully automatically start a new transaction when the last one aborted. The resulting semantics meant my code did the most horrible things. That is indeed horrible, and I'd be running from a DB interface like that as fast as I could. Much of what I've done with PostgreSQL has been with Python (I do a lot of C++ too, but not with databases) and I've thankfully not run into anything like that. psycopg (the alternative PostgreSQL interface for Python) handles exceptions about as well as is possible with PostgreSQL's purely text based exception reporting, and I've found it very useful. I understand that it's also a LOT faster than PyPgSQL, though I don't have any direct experience there as I've never used PyPgSQL. It sounds like I unwittingly dodged a bullet there. As far as I'm concerned any DB interface that's ignoring errors behind your back needs to die. Especially in an exception-capable language like Python, where throwing and letting the upper layers handle it is the obviously sane thing to do. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_stat_user_tables
JackpipE [EMAIL PROTECTED] writes: I'm selecting tables from my db using query: SELECT pg_stat_user_tables.relname FROM pg_stat_user_tables WHERE (pg_stat_user_tables.relname LIKE 'name_hosp_%') The problem I have is that the table names returned by this query are different from the ones in my db. That hardly seems likely, since the contents of that view are drawn directly from pg_class. Maybe you have some old schemas you've forgotten about? 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] SELECT DISTINCT ON and ORDER BY
josep porres escreveu: maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 Wrong. For the op data you will obtain tuples not in original relation. bdteste=# SELECT * FROM foo; id | value | order_field +---+- 1 |10 | 3 2 |12 | 4 3 |10 | 1 4 | 5 | 8 5 |12 | 2 (5 registros) bdteste=# SELECT max(id), value, max(order_field) FROM foo GROUP BY value ORDER BY 3; max | value | max -+---+- 3 |10 | 3 5 |12 | 4 4 | 5 | 8 (3 registros) Try: bdteste=# SELECT * FROM ( SELECT DISTINCT ON (value) id, value, order_field FROM foo ORDER BY value, order_field) AS bar ORDER BY order_field; id | value | order_field +---+- 3 |10 | 1 5 |12 | 2 4 | 5 | 8 (3 registros) Osvaldo -- 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] dunction issue
On Sat, Mar 29, 2008 at 04:05:15AM +0900, Craig Ringer wrote: Sam Mason wrote: ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]'; Just out of interest, what's the lower() function call doing? Absolutely nothing. That's what I get for reading my mail at stupid-o-clock in the morning (Australia) instead of something sensible like sleeping. OK, good to know I wasn't missing something :) I'd almost be tempted to do something like: CREATE DOMAIN emailaddr AS text CHECK (VALUE ~ '^[^ [EMAIL PROTECTED] ]+$'); and then use this instead of text/varchar types. I was thinking about something like that, but my own storage of email addresses actually splits them into user part and domain part (so it can handle the case sensitivity differently - user parts are may be case sensitive depending on the mail system so you can't assume they're the same if they only differ in case; domain parts are never case sensitive) and that would've unnecessarily complicated the example. I didn't think to go for the half way point. I'd never bothered to go that way before. My reasoning being that emails get forwarded, aliases exist and other such fun and games. I only want the unique constraint there to keep my code working. The DB's checks are protecting data integrity and consistency; the UI's checks are improving the user/app interaction by providing early (and usually more friendly) notification of data issues. They're really quite different jobs. Humm, I'm getting the feeling we both learned programming at the same school! Occasionally, though, I do have something where the DB-using app must just submit a request to the DB and see if it works. Either the UI doesn't have the privileges to run the same checks its self, or they're just too expensive to do from the client (or to do twice). In those cases I start to find Pg's error reporting frustrating, and I either resort to a return value sort of approach or embed a unique error code and some parseable values in the exception string. Eg: Some kind of human-readable error description goes here [ERR114:ID=12200;CONFLICTING-ID=] It's not pretty, but it works. sounds sensible. Do any other databaes/other tools work better that you know of? I keep looking for projects, but this could end up touching quite a lot of code. I'm lucky in that I've got a small userbase and they seem to be OK (after a few initial frights) with the raw error messages from the database. Yup, why is this so often ignored when people write database drivers. I used the pyPgSQL python module (I think) for a bit, before realising that it even went as far as to helpfully automatically start a new transaction when the last one aborted. The resulting semantics meant my code did the most horrible things. That is indeed horrible, and I'd be running from a DB interface like that as fast as I could. Yes, luckily I found out reasonably early. I don't do much with python and wanted to see how well it worked. It was a bit of an off putting experience. Much of what I've done with PostgreSQL has been with Python (I do a lot of C++ too, but not with databases) and I've thankfully not run into anything like that. Most of the stuff I do with PG at work is through VB. At least I've managed get away from access at the back end! My little hobby programming things tend to be in much more formally specified things like Haskell, or lower level in C. psycopg (the alternative PostgreSQL interface for Python) handles exceptions about as well as is possible with PostgreSQL's purely text based exception reporting, and I've found it very useful. I understand that it's also a LOT faster than PyPgSQL, though I don't have any direct experience there as I've never used PyPgSQL. It sounds like I unwittingly dodged a bullet there. If I have reason to go back to Python I'll try and remember, thanks! As far as I'm concerned any DB interface that's ignoring errors behind your back needs to die. Especially in an exception-capable language like Python, where throwing and letting the upper layers handle it is the obviously sane thing to do. I think the author was honestly trying to he helpful. It's just that (s)he hadn't quite realised the consequences of this automatic transaction handling. Looking in its readme it's got comments about To achieve the DB-API 2.0 mandated behaviour...a new transaction is created on the next call to execute(). I think maybe something got changed so that it also did an automatic rollback on an exception (I'm pretty sure I'd turned off autocommit pretty early, but it was a while ago). Sam -- 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] Out of memory
On Fri, Mar 28, 2008 at 12:38 PM, Alex Adriaanse [EMAIL PROTECTED] wrote: I have a client that experienced several Out Of Memory errors a few weeks ago (March 10 11), and I'd like to figure out the cause. In the logs it's showing that they were getting out of memory errors for about 0.5-1 hour, after which one of the processes would crash and take the whole database down. After they restarted the server it would eventually start giving out of memory messages and crash again. This happened a total of five times over a 24 hour period. After that we did not see these errors again. They did upgrade to 8.1.11 on the 14th, and have also moved some of the databases to different servers afterwards. First some background information: Software (at the time of the memory errors): CentOS 4.5 (x86_64) running its 2.6.9-55.ELsmp Linux kernel, PostgreSQL 8.1.9 (from RPMs provided on the PostgreSQL web site: postgresql-8.1.9-1PGDG.x86_64). Hardware: 4 dual-core Opterons. 16GB physical RAM, 2GB swap. Database: they use persistent connections, and usually have around 1000 open database connections. The vast majority of those are usually idle. They do run a lot of queries though. The total size of the databases in this cluster is 36GB, with the largest database being 21GB, and the largest table being 2.5GB (having 20 million tuples). Highlights of postgresql.conf settings: max_connections = 2000 shared_buffers = 12 work_mem = 4096 SNIP Just because you can set max_connections to 2000 doesn't mean it's a good idea. If your client needs 1000 persistent connections, then put a connection pooler between your app (I'm guessing php since it operates this way) and the database. Running 1000 connections is a LOT, and you need 1000 active connections, then you're likely gonna need a bigger machine than one with 8 cores and 16 gig of rams. OTOH, if you are actively servicing less than 10% of those connections at a time, then you're wasting memory on the number of backends that are started up and doing nothing. each one consumes some amount of memory on its own, usually in the 5 to 10 meg range, just to sit there and do nothing. Plus you've got issues with thundering herd type situations that can show up as you increase connections. Pooling is the answer here. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Escaping \n
Greetings: We are moving our application from 7.4.14 to 8.3.1. One giant step for mankind... Anyay, I have several triggers that update notes fields in certain tables and loading the trigger function is giving me the following error: psql:bill/bill_preupd_func.plsql:83: WARNING: nonstandard use of escape in a string literal LINE 1: ...OR REPLACE FUNCTION bill_preupd_func () RETURNS TRIGGER AS ' ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. The problem is a line like 'UPDATE bill SET notes = 'blah, blah, yea\nmore stuff'; How to I escape the newline embeded in the string? I've tried the advice from HINT, but have been unable to get it to work. Thanks... Thanks... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.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] Out of memory
Scott Marlowe wrote: Just because you can set max_connections to 2000 doesn't mean it's a good idea. If your client needs 1000 persistent connections, then put a connection pooler between your app (I'm guessing php since it operates this way) and the database. Running 1000 connections is a LOT, and you need 1000 active connections, then you're likely gonna need a bigger machine than one with 8 cores and 16 gig of rams. OTOH, if you are actively servicing less than 10% of those connections at a time, then you're wasting memory on the number of backends that are started up and doing nothing. each one consumes some amount of memory on its own, usually in the 5 to 10 meg range, just to sit there and do nothing. Plus you've got issues with thundering herd type situations that can show up as you increase connections. Pooling is the answer here. Thanks for the suggestion. I fully agree with you, and the client is already making plans to move from persistent connections to connection pooling (they will probably use PgBouncer). I'm just trying to figure out the exact cause of this memory issue so that we can make sure it doesn't happen again, even after they switch to connection pooling. According to my rough calculations, if the server were to max out at 2,000 connections there should still be around 2GB of memory free - unless all of them were running active queries which is doubtful since I did not see unusual changes in the load patterns. And from what I could tell, the server did not tap into swap when PostgreSQL ran out of memory. 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] Escaping \n
On Fri, Mar 28, 2008 at 05:06:10PM -0400, Terry Lee Tucker wrote: HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. The problem is a line like 'UPDATE bill SET notes = 'blah, blah, yea\nmore stuff'; How to I escape the newline embeded in the string? I've tried the advice from HINT, but have been unable to get it to work. The statement would become: UPDATE bill SET notes = E'blah, blah, yea\nmore stuff'; Is this what you tried? I couldn't tell from your message. If you did, then maybe your database drivers are somehow mangling the statement somewhere between your code and the database. You could try running it locally from inside psql to find out. Sam -- 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] Escaping \n
On Friday 28 March 2008 17:21, Sam Mason wrote: On Fri, Mar 28, 2008 at 05:06:10PM -0400, Terry Lee Tucker wrote: HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. The problem is a line like 'UPDATE bill SET notes = 'blah, blah, yea\nmore stuff'; How to I escape the newline embeded in the string? I've tried the advice from HINT, but have been unable to get it to work. The statement would become: UPDATE bill SET notes = E'blah, blah, yea\nmore stuff'; Is this what you tried? I couldn't tell from your message. If you did, then maybe your database drivers are somehow mangling the statement somewhere between your code and the database. You could try running it locally from inside psql to find out. Sam Thanks Sam. No, that is not what I tried. I had tried: UPDATE bill SET notes = 'blah, blah, yeaE'\n'more stuff.' It didn't dawn on me that the E went in front of the whole string! Thanks for the help... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.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] Escaping \n
On Fri, Mar 28, 2008 at 05:29:06PM -0400, Terry Lee Tucker wrote: Thanks Sam. No, that is not what I tried. I had tried: UPDATE bill SET notes = 'blah, blah, yeaE'\n'more stuff.' It didn't dawn on me that the E went in front of the whole string! it's always easy when you know how! Thanks for the help... No probs. PG 8.3 is also more strict with its automatic casts and you may have fun there as well. In previous versions, it would be reasonably happy to (silently) convert lots of datatypes to text. This was generally useful, but occasionally led to bad things happening. Most times I've seen people affected by this is with dates---i.e. to get the year some people did substr(datecol,1,4). In this case you should really be doing extract(year from datecol) because the year doesn't always have to be in the first four digits it just tends to be in the common setups. Sam -- 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] Out of memory
On Fri, Mar 28, 2008 at 01:38:57PM -0500, Alex Adriaanse wrote: First some background information: Software (at the time of the memory errors): CentOS 4.5 (x86_64) running its 2.6.9-55.ELsmp Linux kernel, PostgreSQL 8.1.9 (from RPMs provided on the PostgreSQL web site: postgresql-8.1.9-1PGDG.x86_64). Hardware: 4 dual-core Opterons. 16GB physical RAM, 2GB swap. Since you've clearly disabled overcommit, you should realise that under the default settings you will never use more than about 50-60% of your memory for the database. Your swap will essentially always be unused. The rest will be simply buffers/cache (which may be what you want). You may want to play with the VM settings and/or increase the swap. As for the precise cause of the crash: I don't see anything in the log that would indicate anything unusual, though evidently it wanted about 256MB all of a sudden, which looks suspicously like your maintainence workmem. My guess is that the total allocated VM is fairly close to the limit of your configuration and that a slightly higher than normal load and a background autovacuum took it over the edge. My guess is that if you up the swap to 4GB or perhaps 8GB, you'll get a lot more room to manouver. Does Zenoss track to *allocated* VM in addition to total memory used? Hope this helps, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Merge Joins and Views
Chris Mayfield [EMAIL PROTECTED] writes: See attached -- I've simplified my actual database quite a bit, but this example shows the same results. OK, here's the problem: CREATE VIEW v AS SELECT id, COALESCE(opt, 0) AS opt FROM b; You're using this inside the nullable side of an outer join, and that means the COALESCE() creates a problem: its output won't go to null just because opt does. So the COALESCE has to be evaluated below the outer join, which means that the view can't be flattened into the upper query. You end up with a dumb seqscan that corresponds to planning the view in isolation, and then the best way of joining that with the other table is going to be the sort and merge join. In the case where you introduce the intermediate sub-select, the view *can* be flattened into that, producing SELECT id, COALESCE(opt, 0) AS opt FROM b ORDER BY id Again, that can't be flattened into the top query, but looking at it in isolation the planner chooses an indexscan as the best plan (by no means a sure thing, but it will do it if the index correlation is high). And then the mergejoin without sort falls out from that. So the long and the short of it is that the COALESCE acts as an optimization fence in the presence of outer joins. We've seen this before and there are some rough ideas about fixing it. (In fact, I thought it was on the TODO list, but I can't find an entry now.) Don't hold your breath though --- it'll take major planner surgery. 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] performance impact of using uuid over int4
Hello, Does anybody know of the performance impact of using uuid over int4? Specifically, I am assuming that it will be about 4 times slower since it is 128 bits. Is this correct? Benjamin -- 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] Out of memory
Martijn van Oosterhout [EMAIL PROTECTED] writes: My guess is that the total allocated VM is fairly close to the limit of your configuration and that a slightly higher than normal load and a background autovacuum took it over the edge. My guess is that if you up the swap to 4GB or perhaps 8GB, you'll get a lot more room to manouver. The bits about 2008-03-10 18:43:33 EDT LOG: could not fork new process for connection: Cannot allocate memory certainly suggest that the problem was a global out-of-memory condition and not just within any one process. I concur with raising swap, but also with the upthread suggestions to cut down the number of concurrent connections. The segfaults (sig11s) are a bit disturbing too --- what that probably indicates is someplace using malloc() and failing to test for failure, neither of which is a good thing. Did you by any chance get core dumps from those? A stack trace would be mighty useful. 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] Function for more readable function source code
The only way I knew to display the source code of a function was with
\df+, which produces very hard-to-read output, because it returns
several columns about the function, but usually all I want is the source.
So I created a function (i.e., shameless copied the psql interpreter)
to display just the source code, which makes it much more readable. I'm
passing it along in case it's useful to others as well.
Ken
CREATE FUNCTION function_source( char ) RETURNS text AS $$
DECLARE
funcname ALIAS FOR $1;
source TEXT;
BEGIN
SELECT INTO source replace(p.prosrc,E'\x09',' ')
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
JOIN pg_catalog.pg_roles r ON r.oid = p.proowner
WHERE p.prorettype 'pg_catalog.cstring'::pg_catalog.regtype
AND (p.proargtypes[0] IS NULL
OR p.proargtypes[0] 'pg_catalog.cstring'::pg_catalog.regtype)
AND NOT p.proisagg
AND p.proname ~ ( '^(' || funcname || ')$' )
AND pg_catalog.pg_function_is_visible(p.oid) ;
RETURN source;
END;$$ language 'plpgsql';
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] general optimisation rule for slice of table frequently accessed.
I've 4 tables create table items( item_id serial primary key, attributes... ); create table item_attributes( item_id int references items (item_id) attributes... ); create table baskets( basket_id serial primary key, ...other stuff ); create table basket_items( item_id int references items (item_id), basket_id int references baskets (basket_id), ... ); I pick up the items in a basket with some of their attributes with the join you may expect select [list of columns] from baskets b join basket_items bi on b.basket_=bi.basket_id join items i on i.item_id=bi.item_id join item_attributes a a.item_id=i.item_id where b.basket_id=$1 where the list of columns may change. The above query or small variations of the above query are used frequently in several functions and generally a group of function is called on the same connection with the same $1... The items table is around 600K records, but as you may expect the items in a basket are just a bunch (1 to 20). So I've a cluster of functions referring mostly to the same items but that may require different attributes. I was wondering what would be the best way to share the cost of such retrieval among all function. An idea could be to use a sort of temp table. The first function that need the item list check if a table exists, populate with most of the columns required and at the end of the connection another function clean up... beside the fact I still didn't come up with a solution that will avoid different connection kill each other cache... I'm still wondering what would be the best way to cache the above query or part of the efforts to generate it or some of its variants across cluster of functions. The information in items and items_attributes is quite static. The items in a basket are static across the cluster of function. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Primary Key with serial
Hi, I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1 1 1 2 1 3 2 1 3 1 3 2 Can serial do that? If not, what can i do to make this happen? Thanks a lot
Re: [GENERAL] Out of memory
Thanks everyone for the suggestions so far. Tom Lane wrote: The segfaults (sig11s) are a bit disturbing too --- what that probably indicates is someplace using malloc() and failing to test for failure, neither of which is a good thing. Did you by any chance get core dumps from those? A stack trace would be mighty useful. Unfortunately, we do not have any core dumps from those. Is there anything else I can provide to make tracing this easier? Could we use the addresses mentioned in the segfault messages for anything useful? 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] Primary Key with serial
x asasaxax wrote: Hi, I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1 1 1 2 1 3 2 1 3 1 3 2 Can serial do that? No, a serial type will always be unique. If not, what can i do to make this happen? Re-evaluate your schema. Why do you want these two columns to have such a relationship? If it's very important, don't use a serial for cod and have your application figure out the value to assign. b -- 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] Out of memory
Alex Adriaanse [EMAIL PROTECTED] writes: Unfortunately, we do not have any core dumps from those. Is there anything else I can provide to make tracing this easier? Could we use the addresses mentioned in the segfault messages for anything useful? Hmm, you could try attaching to a running Postgres process with gdb and doing x/i 0xHEXADDRESS --- that should at least come up with a routine name, unless your kernel is into address randomization ... 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] creating a trigger to access another postgres database?
Thanks Richard,
I went through dblink and tried it . But I am not able to pass variables to
sql stmt of dblink_exec function . Basically If I hardcode the values for
updating a table for the sql statement in different database it is working
fine. But What I wanted to do is get the info from existing modified row and
pass that into the update query which I am writing inside dblink_exec call.
my sql inside dblink_exec call would look like
UPDATE my_table set myotherDBTableField = NEW.currDBTableField WHERE
{condition}
When the trigger function executes this it is giving an error some Rule need
to be set.
Whereas If I hardcode that value instead of using NEW. it is working fine?
I appreciate any insights,
thanks,
Richard Huxton [EMAIL PROTECTED] wrote:
carty mc wrote:
I have a question regarding postgres Trigger. We have two
applications which connect to two different databases (Both are
postgres). Is it possible to create a trigger (row based) in one
database say A, which can access another database say B and updates a
table there.
Sure - check the manuals (in 8.3) for dblink - it's in contrib. It's
been there for a while, but the documentation for contrib modules wasn't
in the manual until 8.3
--
Richard Huxton
Archonet Ltd
-
No Cost - Get a month of Blockbuster Total Access now. Sweet deal for Yahoo!
users and friends.
Re: [GENERAL] Using tables in other PostGreSQL database
I have mixed feelings, I agree that pretty much equivalent functionality CAN be delivered using schemas, but some RDBMS's do not have this restriction. Business cases preferences do not necessarily follow database design preferences or capabilities, so irrespective of whether a schema approach CAN work, any user is entitled to ask whether an alternative approach is possible. Enough such users the capability may well be implemented. I am looking to convince a business which does not use schemas, but does use separate databases to move to Postgres having to shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we want to increase the user base of Postgres, we need to meet more users needs. (simplistic I know, but I think a valid concern). For example, any user may need to be able to create databases, add data use referentials in a corporate database of lookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this needs to be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to corporate metadata for data integrity rules to be applied to any database any user creates. Agreed, not a common requirement, but one where schemas are less flexible less secure. Cheers, Brent Wood Scott Marlowe [EMAIL PROTECTED] 29/03/08 4:37 AM On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar [EMAIL PROTECTED] wrote: I am fairly new to Postgres. However, I have to say that I agree with Barry's comments. The real problem here is that you are not using the db properly. You should have one db with all these data in it in different schemas. PostgreSQL provides you with the ability to segregate these data via schemas and fine grained (by the table) ACLs. Your refusal to use multiple schemas in one database due to some perceived problem with them all being in the same database is what's causing your issues. Put your data into various schemas in one database and you can then use access control to decide who sees 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] Fragments in tsearch2 headline
Ah I missed this email. I agree with Teodor that this is not the best way to implement this functionality. At the time I was in a bit of hurry to have something better than the default one and just hacked this. And if we want to have this functionality across languages and parsers it will be better to be implemented in the general framework. The patch takes into account the corner case of overlap. Here is the code for that // start check if (!startHL *currentpos = startpos) startHL = 1; The headline generation will not start until currentpos has gone past startpos. You can also check how this headline function is working at my website indiankanoon.com. Some example queries are murder, freedom of speech, freedom of press etc. Should I develop the patch for the current cvs head of postgres? Thanks, -Sushant. On Mon, 2008-03-17 at 22:00 +0300, Teodor Sigaev wrote: Teodor, Oleg, do we want this? http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php I suppose, we want it. But there are a questions/issues: - Is it needed to introduce new function? may be it will be better to add option to existing headline function. I'd like to keep current layout: ts_headline provides some common interface to headline generation. Finding and marking fragments is deal of parser's headline method and generation of exact pieces of text is made by ts_headline. - Covers may be overlapped. So, overlapped fragments will be looked odd. In any case, the patch was developed for contrib version of tsearch. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL question
Hi List; I have a table that has 3 date columns : create table xyz ( xyz_id integer, date1 timestamp, date2 timestamp, date3 timestamp ) I want to select in a query the xyz_id and the max date column for each row something like : create table temp2 as select xyz_id (max date?) where ... Is this - the (max date?) part a case scenario or is there a better, more efficient method ? Thanks in advance -- 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] SQL question
I have a table that has 3 date columns : create table xyz ( xyz_id integer, date1 timestamp, date2 timestamp, date3 timestamp ) I want to select in a query the xyz_id and the max date column for each row something like : create table temp2 as select xyz_id (max date?) where ... Is this what you want? Select xyz_id, greatest(date1,date2,date3) from xyz where... http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AE N14508 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
