Re: [GENERAL] writing debug output in perl
Harpreet Dhaliwal wrote: > Can anyone help me out with funciton(s) to write a debug > output in a perl function on postgres. Check out DBI-Link and see how it is done there: http://pgfoundry.org/projects/dbi-link/ Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] alter table type from double precision to real
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > On Mon, Jun 25, 2007 at 09:51:30AM +0900, [EMAIL PROTECTED] wrote:
> >> It seems that real takes 8 byte storage sizes.
>
> > Real is 4 bytes but other columns' alignment requirements might
> > result in no space being saved.
>
> Even with no other columns involved, if you're on a machine with
> MAXALIGN = 8 (which includes all 64-bit platforms as well as some
> that aren't), the row width won't shrink.
I see table sizes shrink on 64-bit sparc and x86 architectures, as
in the following example that results in adjacent 4-byte columns.
Or am I misinterpreting what's happening?
test=> create table test (col1 double precision, col2 integer);
CREATE TABLE
test=> insert into test select 1.0, 1 from generate_series(1, 1);
INSERT 0 1
test=> select pg_relation_size('test');
pg_relation_size
--
524288
(1 row)
test=> alter table test alter col1 type real;
ALTER TABLE
test=> select pg_relation_size('test');
pg_relation_size
--
450560
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] alter table type from double precision to real
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Mon, Jun 25, 2007 at 09:51:30AM +0900, [EMAIL PROTECTED] wrote: >> But PostgreSQL's data disk usage did not shrinked. >> And pg_dump size remained same. >> It seems that real takes 8 byte storage sizes. > Real is 4 bytes but other columns' alignment requirements might > result in no space being saved. Even with no other columns involved, if you're on a machine with MAXALIGN = 8 (which includes all 64-bit platforms as well as some that aren't), the row width won't shrink. As for that pg_dump measurement, the text form isn't going to get smaller ... "1.2" is the same length as "1.2". regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] alter table type from double precision to real
On Mon, Jun 25, 2007 at 09:51:30AM +0900, [EMAIL PROTECTED] wrote: > Real type takes 4 byte storage sizes and double precision takes 8 bytes. > I altered a data type from double precision to real and vacuumed DB. Altering a column's type rewrites the table so vacuuming afterward shouldn't be necessary. > But PostgreSQL's data disk usage did not shrinked. > And pg_dump size remained same. > It seems that real takes 8 byte storage sizes. Real is 4 bytes but other columns' alignment requirements might result in no space being saved. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] alter table type from double precision to real
Real type takes 4 byte storage sizes and double precision takes 8 bytes. I altered a data type from double precision to real and vacuumed DB. But PostgreSQL's data disk usage did not shrinked. And pg_dump size remained same. It seems that real takes 8 byte storage sizes. Here's my environments: CPU: AMD Opteron OS: Solaris 10 PostgreSQL: 8.2.4, compiled w/ Sun CC using 64-bit flags ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] how to implement unusual constraint
"So frequently the best advice for someone who's thinking of doing something like this is "redesign your schema so you don't need to". " I've thought about that. The obvious way to do it would be to split into two tables, one for the originals, one for the translations (the objects are actually phrases in many languages). But I'd rather avoid that because in all other ways, the objects have the same properties, reference the same objects, and so on. Splitting into two types is not really desirable, from the point of view of what they are modelling. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] low transaction ID wrap limit
Jeff Amiel <[EMAIL PROTECTED]> writes: > I was looking at some logs this morning and spotted this on my backup > database > Jun 24 04:08:46 back-app-1 postgres[82445]: [2-1] pgsql 82445 LOG: > transaction ID wrap limit is 41612954, limited by database "back" > Should I be worried? No. It's just wrapped past 4 billion. The age(datfrozenxid) numbers show you are in good shape; nothing particularly out-of-date except template0, which doesn't need vacuuming anyway. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to implement unusual constraint
Ragnar <[EMAIL PROTECTED]> writes: > On sun, 2007-06-24 at 09:54 +, danmcb wrote: >> Say I have a table, say my_table, that is self-referencing. ... >> in other words: the row pointed to by orig_id cannot reference any row >> other than itself. >> How might I implement this as a constraint? > you can get around the limitation that subqueries are not allowed in > CHECK constraints by using a function. In general that's a last-ditch measure that's best avoided. It's got two serious problems: 1. You've got to write explicit code for both ends of the constraint; for example, prevent a row from being changed to have orig_id != id if there are any rows linking to it. (And a check constraint cannot act at row deletion at all, so you'll still need the foreign key constraint to prevent deletion of a referenced row.) 2. There is no way to defend against contradictory concurrent updates, since neither check constraint can "see" uncommitted changes of other transactions. (Hmm ... actually you can probably work around that with suitable use of SELECT FOR UPDATE or SELECT FOR SHARE, not plain SELECT, in the checking function. But it's a whole 'nother layer of complexity for you to deal with.) The good thing about foreign key constraints is that those problems are already solved for you. So frequently the best advice for someone who's thinking of doing something like this is "redesign your schema so you don't need to". regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] low transaction ID wrap limit
Whenever I read the documentation on the transaction wraparound stuff,
my head spins with all the references to frozen xids and min/max ages.
When it comes down to it, my interpretation of the whole kaboodle is
"run postgresql v8.1 or later and autovacuum, and you will not have
to deal with the wraparound issue."
I was looking at some logs this morning and spotted this on my backup
database
Jun 24 04:08:46 back-app-1 postgres[82445]: [2-1] pgsql 82445 LOG: transaction ID wrap
limit is 41612954, limited by database "back"
That seemed like an awfully low number compared to the 1 or 2 billion that I
have seen in other posts on the subject.
postgres=# SELECT datname, age(datfrozenxid) FROM pg_database;
datname |age
---+
postgres | 1073968236
back | 1079399370
template0 | 1657840460
template1 | 1078788693
postgres=# select * from pg_database;
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit |
datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig |
datacl
---++--+---+--+--+---+--+--+---+---+--
postgres | 10 |0 | f | t| -1 |
10792 | 3268269560 | 2194527737 | 1663 | |
back | 10 |0 | f | t| -1 |
10792 | 3268269655 | 2194527832 | 1663 | |
template0 | 10 |0 | t | f| -1 |
10792 | 1610655513 | 1610655513 | 1663 | |
{pgsql=CT/pgsql}
template1 | 10 |0 | t | t| -1 |
10792 | 326849 | 2194754451 | 1663 | |
{pgsql=CT/pgsql}
(4 rows)
Should I be worried?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] how to implement unusual constraint
On sun, 2007-06-24 at 09:54 +, danmcb wrote: > Say I have a table, say my_table, that is self-referencing. It looks > like this : > id integer pk, > orig_id integer references my_table(id), > Now this set of rows would be legal > > id/orig_id > 1 /1 > 2/1 > 3/1 > 4/4 > 5/4 > > but this not: > > id/orig_id > 1 /1 > 2/1 > 3/1 > 4/1 > 5/4 > > in other words: the row pointed to by orig_id cannot reference any row > other than itself. > How might I implement this as a constraint? you can get around the limitation that subqueries are not allowed in CHECK constraints by using a function. this might get you on the right track: test=# create table foo (i int, o int); CREATE TABLE test=# create function foo_check(int) returns int language SQL AS 'select o from foo where i=$1'; CREATE FUNCTION test=# alter table foo ADD CHECK (foo_check(o)=o); ALTER TABLE test=# insert into foo values (1,1); INSERT 0 1 test=# insert into foo values (2,1); INSERT 0 1 test=# insert into foo values (3,1); INSERT 0 1 test=# insert into foo values (4,3); ERROR: new row for relation "foo" violates check constraint "foo_o_check" test=# insert into foo values (4,4); INSERT 0 1 test=# insert into foo values (5,4); INSERT 0 1 of course this example was very incomplete. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Duplicate Key Violates Unique Contraint whenUpdating a table
On sun, 2007-06-24 at 17:58 +0800, carter ck wrote: > >From: Ragnar <[EMAIL PROTECTED]> > >On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: > > > I am experiencing thgis problem since this morning. I seldom heard about > > > unique key constraint violation to happen when updating a table. > > > > > > It happens to 1 or few records at early stage, but then to all. > > > > > > My updating command is as following: > > > > > > update mytable set my_status='Y' where myid='ABC123567778'; > > > > > > Can anyone help? All helps and solutions are appreciated. > > > >sounds like you have a UNIQUE constraint involving the column > >"my_status". > > > >did you recently create a new index on this table? > > > >what does psql say to: > >\d mytable > > > > No. The only unique key is the myid, which is also the primary key. I have > extracted the records out from this database and dump all to a new database. > Everything is fine. But, this is the second time I have encountered this > problem. do you still have the old database around? if you do, does a REINDEX mytable; make your problem go away? gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate Key Violates Unique Contraint whenUpdating a table
Hi.. No. The only unique key is the myid, which is also the primary key. I have extracted the records out from this database and dump all to a new database. Everything is fine. But, this is the second time I have encountered this problem. Hopefully those who have solutions for this can kindly post a feedback. THanks. From: Ragnar <[EMAIL PROTECTED]> To: carter ck <[EMAIL PROTECTED]> CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Duplicate Key Violates Unique Contraint whenUpdating a table Date: Sun, 24 Jun 2007 10:05:16 + On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: > I am experiencing thgis problem since this morning. I seldom heard about > unique key constraint violation to happen when updating a table. > > It happens to 1 or few records at early stage, but then to all. > > My updating command is as following: > > update mytable set my_status='Y' where myid='ABC123567778'; > > Can anyone help? All helps and solutions are appreciated. sounds like you have a UNIQUE constraint involving the column "my_status". did you recently create a new index on this table? what does psql say to: \d mytable gnari _ Get the new Windows Live Messenger! http://get.live.com/messenger/overview ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] how to implement unusual constraint
Say I have a table, say my_table, that is self-referencing. It looks like this : id integer pk, orig_id integer references my_table(id), . . . Now this set of rows would be legal id/orig_id 1 /1 2/1 3/1 4/4 5/4 but this not: id/orig_id 1 /1 2/1 3/1 4/1 5/4 in other words: the row pointed to by orig_id cannot reference any row other than itself. How might I implement this as a constraint? thanks, Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate Key Violates Unique Contraint when Updating a table
On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: > I am experiencing thgis problem since this morning. I seldom heard about > unique key constraint violation to happen when updating a table. > > It happens to 1 or few records at early stage, but then to all. > > My updating command is as following: > > update mytable set my_status='Y' where myid='ABC123567778'; > > Can anyone help? All helps and solutions are appreciated. sounds like you have a UNIQUE constraint involving the column "my_status". did you recently create a new index on this table? what does psql say to: \d mytable gnari ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] finding items with 0 rels for a 0 to many relationship
thanks both for this. I haven't got around to writing this part of the code yet, but will do soon. I appreciate the pointers. On 21 Jun, 19:13, [EMAIL PROTECTED] (Michael Glaesemann) wrote: > On Jun 21, 2007, at 11:57 , Josh Tolley wrote: > > > > > On 6/21/07, danmcb <[EMAIL PROTECTED]> wrote: > >> Hi > > >> I have two tables, say A and B, that have a many-to-many > >> relationship, implemented in the usual way with a join table A_B. > > >> How can I economically find all the rows in table A whose id's are > >> not > >> in A_B at all (i.e. they have zero instances of B associated)? > > > Use a left join. For instance, say there are a.id and b.id columns, > > which are the primary keys in A and B respectively. Also say A_B > > contains columns aid and bid which reference a.id and b.id > > respectively. > > >> SELECT * FROM A LEFT JOIN A_B ON (A.ID = A_B.AID) WHERE A_B.BID IS > >> NULL; > > Alternatively you can use EXCEPT. Using Josh's schema: > > SELECT id > FROM A > EXCEPT > SELECT aid > FROM A_B. > > You'll want to check with EXPLAIN ANALYZE, but in general I suspect > the outer join is faster. > > Michael Glaesemann > grzm seespotcode net > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2 contrib. "Full Disjunction"
On Sat, Jun 23, 2007 at 10:33:49PM +0100, Gregory Stark wrote: > "Martijn van Oosterhout" <[EMAIL PROTECTED]> writes: > > On Fri, Jun 22, 2007 at 07:38:01PM +0300, Tzahi Fadida wrote: > >> Let me simplify it in lamer terms. > >> Basically, you have a cycle in your relations schema. i.e. > >> rel A: att-x, att-y > >> rel B: att-y, att-z > >> rel C: att-z, att-x > > I'm still lost. I can see how it would be hard to join these together but I'm > not sure what result I would be after. Well, the way I understand it is if you had the following data: rel A x : y 1 : 2 5 : 6 rel B: y : z 2 : 3 7 : 8 rel C: z : x 3 : 1 10 : 9 That the result would be: x : y : z 1 : 2 : 3 5 : 6 : : 7 : 8 9 : : 10 Now, I can't off the top of my head think of a schema where you would need this, but if you have this problem then I don't see the solution in plain SQL. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] Duplicate Key Violates Unique Contraint when Updating a table
Hi all, I am experiencing thgis problem since this morning. I seldom heard about unique key constraint violation to happen when updating a table. It happens to 1 or few records at early stage, but then to all. My updating command is as following: update mytable set my_status='Y' where myid='ABC123567778'; Can anyone help? All helps and solutions are appreciated. THanks. CK _ Check it out! Windows Live Spaces is here! http://spaces.live.com/?mkt=en-sg ItÂ’s easy to create your own personal Web site. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
