Re: [SQL] pg primary key bug?

[Tom Lane]

pginfo <[EMAIL PROTECTED]> writes:
> In this table we store the last value for the ID of part from other tables.
> For each table we have one constant in this table. We are using the 
> table as sequence.
> For Example if we nee to insert the next record in some table we make:
> select constvalue from a_constants_str where constname ='...' for update;
> increase the value and make
> update a_constants_str set   constvalue= (new value) where...

> It is not so easy as I described, but in general this is the case.
> Al this is connected with replications and data syncronisation and so on.

"Connected"?  What exactly is hiding under that last comment?

One way I could take your report is that you've found a weird
interaction between SELECT FOR UPDATE and VACUUM FULL that no one else
has seen before.  Another way is that you're using some nonstandard
backend extension that has nasty bugs in it.

It is interesting that you say this system has been working well for
years and only recently have you seen problems.  To me the obvious
question is "what have you changed recently?"  It might not be a bogus
change in itself, but it could have triggered a bug at lower levels.

It's certainly possible that you have your finger on a backend bug,
but if so there's not nearly enough information here for anyone to
find and fix it.  You need to be thinking in terms of how to reproduce
the problem so that it can be studied and fixed.  "How can I avoid this
problem" is exactly the wrong question to be asking, because even if
avoiding it is all that concerns you, no one can answer with any
confidence until we understand what the failure mechanism is.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] pg primary key bug?

[pginfo]

Tom Lane wrote:

  pginfo <[EMAIL PROTECTED]> writes:
  
  
In this table we store the last value for the ID of part from other tables.
For each table we have one constant in this table. We are using the 
table as sequence.
For Example if we nee to insert the next record in some table we make:
select constvalue from a_constants_str where constname ='...' for update;
increase the value and make
update a_constants_str set   constvalue= (new value) where...

  
  
  
  
It is not so easy as I described, but in general this is the case.
Al this is connected with replications and data syncronisation and so on.

  
  
"Connected"?  What exactly is hiding under that last comment?
  

We are using separate table for sequences and not sequences from pg direct,
because this is built in application method for making 
replication and data syncro.
I wish only to clarify the reason of using the table and to describe the
groud for so many updates and select for updates.
Sorry for my bad english ):.

  
One way I could take your report is that you've found a weird
interaction between SELECT FOR UPDATE and VACUUM FULL that no one else
has seen before.  Another way is that you're using some nonstandard
backend extension that has nasty bugs in it.

It is interesting that you say this system has been working well for
years and only recently have you seen problems.

Yes, exact.

To me the obvious
question is "what have you changed recently?"

If I know !
In general we do not make any global changes connected to database access
method.
We are using  jdbc (jdbc driver from pg)  + jboss (java based application
server) + connection pool (biult in jboss).
We are using jdbc with Statement.executeBatch(...) and also direct with Statement.executeUpdate(...)
.
We are using exact the same ide with oracle without any problem ( oracle
have anoder problems and I prefer pg).

It might not be a bogus
change in itself, but it could have triggered a bug at lower levels.

It's certainly possible that you have your finger on a backend bug,
but if so there's not nearly enough information here for anyone to
find and fix it.

I am sure (not 100%) that it is bug. That is the reason to report the problem.


You need to be thinking in terms of how to reproduce
the problem so that it can be studied and fixed.

You idea was that we have "vacuum full" + update or select for update in
the same time.
I think it is not the case, because we start vacuum full at 1:00 AM and no
one is working in this time.

Will vacuum full generate this problem if we have locked table in this time?
(It is possible to have locked table in theory)

At this time we do not have info about how to reproduce the problem.
As the first step we will stop using "vacum full" (if needet we will stop
using vacuum analyze too) to try to collect more info.



"How can I avoid this
problem" is exactly the wrong question to be asking, because even if
avoiding it is all that concerns you, no one can answer with any
confidence until we understand what the failure mechanism is.
  

Can we set some log parameters to collect the needet data?
Can you describe more detailed the idea of  problem with "vacuum full" +
"update" and can some one make patch if this problem exists in theory (if
I  understand you right)?
We can start using this patch and see if the problem will be again .

If you have anoder Idea we are ready to collect the needet data.

  
			regards, tom lane


  

regards,
ivan.

Re: [SQL] pg primary key bug?

[Ragnar Hafstað]

On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote:
> 
>   
> We are using  jdbc (jdbc driver from pg)  + jboss (java based
> application server) + connection pool (biult in jboss).
> ...
> Will vacuum full generate this problem if we have locked table in this
> time? (It is possible to have locked table in theory)

I do not know if this is relevant, but I have seen jboss applications
keep sessions in 'Idle in transaction' state, apparently with some
locks granted. Would such cases not interfere with vacuum?

gnari



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] pg primary key bug?

[pginfo]

Ragnar Hafstað wrote:

  On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote:
  
  
  
We are using  jdbc (jdbc driver from pg)  + jboss (java based
application server) + connection pool (biult in jboss).
...
Will vacuum full generate this problem if we have locked table in this
time? (It is possible to have locked table in theory)

  
  
I do not know if this is relevant, but I have seen jboss applications
keep sessions in 'Idle in transaction' state, apparently with some
locks granted. Would such cases not interfere with vacuum?

gnari

  

Only to add,
also keeping sme transactions for long time not commited (possible).
regards,
ivan.

  


  

[SQL] problem inserting local characters ...

[Garry]

Hi,
I'm running PostGreSql 7.4.6 on SuSE 9.1. Everything as such is working 
fine. In order to import some data from an old system, I create an SQL 
script that does something like:

insert into prg_contacts values ('2005-02-22 13:45:36+01','NETHINKS 
Converter',NULL,NULL,'admin','s',
NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,
NULL,'Anrede','Titel','Vorname',
NULL,'Firmenname','TelefonGeschäft','Faxnr',
'TelefonMobil','[EMAIL PROTECTED]',NULL,NULL,
'SMS-Pager','[EMAIL PROTECTED]','Firmenland','FirmenOrt',
'FirmenPLZ',NULL,'Firmenbundesland','Firmenstraße',
NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
[..]

Trying to do this insert, I get an error on both the values 
"TelefonGeschäft" and "Firmenstraße":

FEHLER:  ungültige Byte-Sequenz für Kodierung »UNICODE«: 0xe165
(the 0xe165 differs between the two; the fields in question are regular 
type "text" fields)

Looks like I'm having some trouble with unicode encoding ... The 
characters I have are regular 8bit ASCII chars ... How can I fix this?

Help appreciated, -gg
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] problem inserting local characters ...

[Markus Schaber]

Hi, Garry,

Garry schrieb:

> Trying to do this insert, I get an error on both the values
> "TelefonGeschäft" and "Firmenstraße":
> 
> FEHLER:  ungültige Byte-Sequenz für Kodierung »UNICODE«: 0xe165

This sounds as your database was created in unicode (utf-8).

> (the 0xe165 differs between the two; the fields in question are regular
> type "text" fields)
> 
> Looks like I'm having some trouble with unicode encoding ... The
> characters I have are regular 8bit ASCII chars ... How can I fix this?

No, 8bit ASCII does not exist. ASCII always is 7 bit. As your error
message is in German, I suspect your data is encoded in LATIN1 or LATIN9
(their only difference is the EUR symbol in the latter one).

Can you try to add the following command before your insert statements:

set client_encoding to latin1;

HTH,
Markus

-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com



signature.asc
Description: OpenPGP digital signature

Re: [SQL] pg primary key bug?

[Markus Schaber]

Hi, Ragnar,

Ragnar Hafstað schrieb:

>>We are using  jdbc (jdbc driver from pg)  + jboss (java based
>>application server) + connection pool (biult in jboss).
>>...
>>Will vacuum full generate this problem if we have locked table in this
>>time? (It is possible to have locked table in theory)
> I do not know if this is relevant, but I have seen jboss applications
> keep sessions in 'Idle in transaction' state, apparently with some
> locks granted. Would such cases not interfere with vacuum?

Most of those "Idle in transaction" problems were caused by suboptimal
handling of BEGIN in the pgjdbc driver, this should be fixed in current
versions of postgres.jar (build 8.0-310).

Markus

-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com



signature.asc
Description: OpenPGP digital signature

Re: [SQL] Working with XML.

[George Weaver]

Title: Message



Hi Theo,
 
I am not able to duplicate the problem you 
experienced.  I tried the query you provided below using pgAdmin and 
psql on a 7.3.2 and an 8.0 database with success.  I then copied all the 
rows and pasted them to the end so that I would have 100 rows, and 
the query worked as expected.
 
Perhaps the client you're using is causing the 
problem.  Can you run the query in pgAdmin?
 
Regards,
George

  - Original Message - 
  From: 
  Theo Galanakis 
  To: 'George Weaver' 
  Cc: 'pgsql-sql@postgresql.org' 
  Sent: Monday, February 21, 2005 11:54 
  PM
  Subject: RE: [SQL] Working with 
XML.
  
  Thankyou George,
   
  XML2 
  compiled ok... next stummbling block..  when I pass a very long XML 
  string to xpath_string() it crashes the postgres server. Client receives 
  a message like:
   
  server closed the connection unexpectedlyThis 
  probably means the server terminated abnormallybefore or while processing 
  the request.
   
  Below is the function call, a smaller xml string with 
  say 40 records works fine, anything bigger crashes the 
  server.
   
  select xpath_string(' 7741872226632005-02-22 
  

Re: [SQL] problem inserting local characters ...

[Joel Fradkin]

I had a similar problem myself and found if I used SQL_ASCHII for the data
base I could insert my data.

I understand however that it basically means the database does not know
anything about encoding and therefore stuff like upper does not work on the
extended chars.

I ended up changing to sql_aschii so both my inserts and backup and restore
work, realizing I am giving up some functionality.

Joel Fradkin
 
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
 
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
 

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Markus Schaber
Sent: Tuesday, February 22, 2005 8:41 AM
To: Garry
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] problem inserting local characters ...

Hi, Garry,

Garry schrieb:

> Trying to do this insert, I get an error on both the values
> "TelefonGeschäft" and "Firmenstraße":
> 
> FEHLER:  ungültige Byte-Sequenz für Kodierung »UNICODE«: 0xe165

This sounds as your database was created in unicode (utf-8).

> (the 0xe165 differs between the two; the fields in question are regular
> type "text" fields)
> 
> Looks like I'm having some trouble with unicode encoding ... The
> characters I have are regular 8bit ASCII chars ... How can I fix this?

No, 8bit ASCII does not exist. ASCII always is 7 bit. As your error
message is in German, I suspect your data is encoded in LATIN1 or LATIN9
(their only difference is the EUR symbol in the latter one).

Can you try to add the following command before your insert statements:

set client_encoding to latin1;

HTH,
Markus

-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] pg primary key bug?

[Tom Lane]

pginfo <[EMAIL PROTECTED]> writes:
> You idea was that we have "vacuum full" + update or select for update in 
> the same time.
> I think it is not the case, because we start vacuum full at 1:00 AM and 
> no one is working in this time.

Hmm.  AFAICT the duplicate row copies could only be produced by vacuum
full, so that's certainly part of the issue.  But if vacuum full in
isolation were broken, we'd surely know it; so there must be some other
contributing factor involved that your setup is exercising but other
people are (mostly) not doing.

I agree with the plan to use plain vacuum for awhile and see if that
makes the problem go away.  I think it would have to, but maybe I'm
all wet about that.

In the meantime I would suggest seeing if you can distill your
application down into a test case that other people can run to reproduce
the problem.  It doesn't matter if the test doesn't make the bug happen
very often, but we have to see the problem happening before we have much
hope of fixing it.

> Will vacuum full generate this problem if we have locked table in this 
> time? (It is possible to have locked table in theory)

No, that's hardly likely.  vacuum full deals with locks all the time.

> Can you describe more detailed the idea of  problem with "vacuum full" + 
> "update" and can some one make patch if this problem exists in theory 
> (if I  understand you right)?

I have no idea what the actual failure mechanism might be.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] pg primary key bug?

[pginfo]

Tom Lane wrote:

  pginfo <[EMAIL PROTECTED]> writes:
  
  
You idea was that we have "vacuum full" + update or select for update in 
the same time.
I think it is not the case, because we start vacuum full at 1:00 AM and 
no one is working in this time.

  
  
Hmm.  AFAICT the duplicate row copies could only be produced by vacuum
full, so that's certainly part of the issue.  But if vacuum full in
isolation were broken, we'd surely know it; so there must be some other
contributing factor involved that your setup is exercising but other
people are (mostly) not doing.

I agree with the plan to use plain vacuum for awhile and see if that
makes the problem go away.  I think it would have to, but maybe I'm
all wet about that.
  

Ok, we can still using vacuum full on some installs (with risk to make problems
to customes).
I will to ask if it will be possible to start some querys (I do not know
the query) exactly before running vacuum full and to save the results in
some log file. If it is possible, we will be able to post the results to
the list in case of  ne problem and to have some start point for reproducing
the problem. My idea is some one more familiar with pg to send this querys
(if it exists) and we will install it in vacuum scripts.


  
In the meantime I would suggest seeing if you can distill your
application down into a test case that other people can run to reproduce
the problem.  It doesn't matter if the test doesn't make the bug happen
very often, but we have to see the problem happening before we have much
hope of fixing it.

  
  
Will vacuum full generate this problem if we have locked table in this 
time? (It is possible to have locked table in theory)

  
  
No, that's hardly likely.  vacuum full deals with locks all the time.

  
  
Can you describe more detailed the idea of  problem with "vacuum full" + 
"update" and can some one make patch if this problem exists in theory 
(if I  understand you right)?

  
  
I have no idea what the actual failure mechanism might be.

			regards, tom lane
  

regards,
ivan.

  

  

Re: [SQL] pg primary key bug?

[Tom Lane]

pginfo <[EMAIL PROTECTED]> writes:
> I will to ask if it will be possible to start some querys (I do not know 
> the query) exactly before running vacuum full and to save the results in 
> some log file. If it is possible, we will be able to post the results to 
> the list in case of  ne problem and to have some start point for 
> reproducing the problem.

Well, you should definitely turn on log_statement across the whole
installation so that you have a complete record of all SQL commands
being issued.  Make sure the log includes timestamps and PIDs.

I would suggest adding a simple probe for duplicate records to the
vacuum script.  Maybe something like

set enable_indexscan to off;
select constname,fid,count(*) from a_constants_str
group by constname,fid having count(*) > 1;

(The indexscan off bit is just paranoia --- I think that an indexscan
might mask the presence of multiple copies of what's supposedly a unique
key.)  Do this just before and just after the vacuum full command.  That
will at least nail down whether vacuum full is creating the dups, and
once we see it happen the trace of the day's SQL commands may give some
ideas where to look.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] pg primary key bug?

[pginfo]

Tom Lane wrote:

  pginfo <[EMAIL PROTECTED]> writes:
  
  
I will to ask if it will be possible to start some querys (I do not know 
the query) exactly before running vacuum full and to save the results in 
some log file. If it is possible, we will be able to post the results to 
the list in case of  ne problem and to have some start point for 
reproducing the problem.

  
  
Well, you should definitely turn on log_statement across the whole
installation so that you have a complete record of all SQL commands
being issued.  Make sure the log includes timestamps and PIDs.

I would suggest adding a simple probe for duplicate records to the
vacuum script.  Maybe something like

	set enable_indexscan to off;
	select constname,fid,count(*) from a_constants_str
	group by constname,fid having count(*) > 1;

(The indexscan off bit is just paranoia --- I think that an indexscan
might mask the presence of multiple copies of what's supposedly a unique
key.)  Do this just before and just after the vacuum full command.  That
will at least nail down whether vacuum full is creating the dups, and
once we see it happen the trace of the day's SQL commands may give some
ideas where to look.
  

Ok, it was my idea.
We will do it and install the script in ~100 servers and will see the result.

  
			regards, tom lane


  

regards,
ivan.

[SQL] schemas and paths with the alter statement

[Theodore Petrosky]

I have to first admit that I am very green at this. I
thought that one could refer to a table in a fully
qualified path... public.testtable

I am fooling around with webobjects and EOModeler. at
one point it creates the sql to create my table
structure and it does something like:

ALTER TABLE public.test ADD CONSTRAINT public.test_PK
PRIMARY KEY (test);

I assumed that it would work. Did I miss something in
the docs that say you can not do this? Does "ALTER
TABLE" not work with 'public.' before the table name?

on 7.4.6

aswebtest=# ALTER TABLE public.test ADD CONSTRAINT
public.test_PK PRIMARY KEY (test);
ERROR:  syntax error at or near "." at character 46

but it works if I remove the 'public.' from both
places

aswebtest=# ALTER TABLE test ADD CONSTRAINT test_PK
PRIMARY KEY (test);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create
implicit index "test_pk" for table "test"
ALTER TABLE


Ted



__ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] schemas and paths with the alter statement

[Tom Lane]

Theodore Petrosky <[EMAIL PROTECTED]> writes:
> ALTER TABLE public.test ADD CONSTRAINT public.test_PK
> PRIMARY KEY (test);

Constraints don't have schema-qualified names.  Try

ALTER TABLE public.test ADD CONSTRAINT test_PK PRIMARY KEY (test);

The error message was pointing to the correct place BTW.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] schemas and paths with the alter statement

[Richard Huxton]

Theodore Petrosky wrote:
I assumed that it would work. Did I miss something in
the docs that say you can not do this? Does "ALTER
TABLE" not work with 'public.' before the table name?
on 7.4.6
aswebtest=# ALTER TABLE public.test ADD CONSTRAINT
public.test_PK PRIMARY KEY (test);
ERROR:  syntax error at or near "." at character 46
Are you sure it's not the constraint that is causing the problem (he 
says, counting 46 characters in). I think a table constraint is by 
definition in the same schema as its table.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 3: 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: [SQL] schemas and paths with the alter statement

[Ragnar Hafstað]

On Tue, 2005-02-22 at 11:06 -0800, Theodore Petrosky wrote:
> I have to first admit that I am very green at this. I
> thought that one could refer to a table in a fully
> qualified path... public.testtable
> ...
> ALTER TABLE public.test ADD CONSTRAINT public.test_PK
> PRIMARY KEY (test);
> ...
> aswebtest=# ALTER TABLE public.test ADD CONSTRAINT
> public.test_PK PRIMARY KEY (test);
> ERROR:  syntax error at or near "." at character 46

it not the first public that is the problem, but the
second one, as you might discover if you count 46
characters.

you cannot put the constraint in a different schema than
the table, it seems.

gnari





---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] schemas and paths with the alter statement

[Theodore Petrosky]

Wow...in less than 5 minutes

Thanks

Ted

--- Tom Lane <[EMAIL PROTECTED]> wrote:

> Theodore Petrosky <[EMAIL PROTECTED]> writes:
> > ALTER TABLE public.test ADD CONSTRAINT
> public.test_PK
> > PRIMARY KEY (test);
> 
> Constraints don't have schema-qualified names.  Try
> 
> ALTER TABLE public.test ADD CONSTRAINT test_PK
> PRIMARY KEY (test);
> 
> The error message was pointing to the correct place
> BTW.
> 
>   regards, tom lane
> 



__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

---(end of broadcast)---
TIP 3: 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

[SQL] aggregate / group by question

[T E Schmitz]

Hello,
I must apologize for not coming up with a more descriptive subject line.
I am struggling with the following query and I am not even sure whether 
what I want to achieve is possible at all:

The problem in real-world terms: The DB stores TRANSAKTIONS - which are 
either sales or refunds: each TRANSAKTION has n ITEMS related to it, 
which contain their RETAIL_PRICE and DISCOUNT. At the end of day, a 
total is run up, which should show the sum of refunds, sales and discounts.

Tables:
TRANSAKTION
---
KIND ('R' or 'S' for refund or sale)
TRANSAKTION_PK
PAYMENT_METHOD (cheque, cash, CC)
ITEM

TRANSAKTION_FK
ITEM_PK
RETAIL_PRICE
DISCOUNT
Desired result set:
PAYMENT_METHOD | category | SUBTOTAL

Cash   | sales| 103,55
Cash   | discounts|  -0,53
Cash   | refunds  | -20,99
CC | sales| 203,55
CC | discounts|  -5,53
CC | refunds  | -25,99
where
sales amount is the sum of RETAIL_PRICE
discount amount is the sum of DISCOUNT
refunds is the sum of (RETAIL_PRICE-DISCOUNT)
I've had a stab at it but my sales amount is short of the RETAIL_PRICEs 
of all discounted ITEMs:

select PAYMENT_METHOD,
case
when KIND='R' then 'R'
when KIND='S' and DISCOUNT is not null then 'D'
when KIND='S' and DISCOUNT is  null then 'S'
end as CATEGORY,
sum(case
when KIND=2 then -(RETAIL_PRICE-coalesce(DISCOUNT,0))
when KIND=1 and DISCOUNT is not null then -DISCOUNT
when KIND=1 and DISCOUNT is null then RETAIL_PRICE
end) as SUBTOTAL,
from ITEM
inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK
where ...
group by PAYMENT_METHOD,CATEGORY
order by PAYMENT_METHOD,CATEGORY
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] how can I query for unset timestamps

[Bret Hughes]

I have a table that I recently altered by adding several timestamp
columns.  I would like to query the table for those rows for which the
timestamp has not been set but am unable to find a way to do so.  any
tips ?  in psql the column appears empty and char_length returns
nothing.

Some of what I have tried 

"" -> not valid

char_length(last_timestamp) > 0  -> returns the rows with the time
stamps

where NOT char_length(last_timestamp) > 0  -> returns 0 rows

where NOT (char_length(last_timestamp) > 0 ) -> returns 0 rows

where last_timestamp = NULL;  -> 0 rows

this is kicking my butt, and starting to piss me off so if any one has
tips I would appreciate it.

Bret




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] how can I query for unset timestamps

[Gregory S. Williamson]

Bret --
A test for NULL uses the IS statement, e.g.:

SELECT * FROM tablefoo WHERE last_timestamp IS NULL;

HTH,

Greg Williamson
DBA
GlobeXPlorer LLC

-Original Message-
From:   Bret Hughes [mailto:[EMAIL PROTECTED]
Sent:   Tue 2/22/2005 1:09 PM
To: postgresql sql list
Cc: 
Subject:[SQL] how can I query for unset timestamps
I have a table that I recently altered by adding several timestamp
columns.  I would like to query the table for those rows for which the
timestamp has not been set but am unable to find a way to do so.  any
tips ?  in psql the column appears empty and char_length returns
nothing.

Some of what I have tried 

"" -> not valid

char_length(last_timestamp) > 0  -> returns the rows with the time
stamps

where NOT char_length(last_timestamp) > 0  -> returns 0 rows

where NOT (char_length(last_timestamp) > 0 ) -> returns 0 rows

where last_timestamp = NULL;  -> 0 rows

this is kicking my butt, and starting to piss me off so if any one has
tips I would appreciate it.

Bret




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

!DSPAM:421b9fba271461201672223!





---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] how can I query for unset timestamps

[Bret Hughes]

Well, thank you very much.  works perfectly, I guess I have not done
much NULL value stuff.  

Bret

On Tue, 2005-02-22 at 15:19, Gregory S. Williamson wrote:
> Bret --
> A test for NULL uses the IS statement, e.g.:
> 
> SELECT * FROM tablefoo WHERE last_timestamp IS NULL;
> 
> HTH,
> 
> Greg Williamson
> DBA
> GlobeXPlorer LLC
> 
> -Original Message-
> From: Bret Hughes [mailto:[EMAIL PROTECTED]
> Sent: Tue 2/22/2005 1:09 PM
> To:   postgresql sql list
> Cc:   
> Subject:  [SQL] how can I query for unset timestamps
> I have a table that I recently altered by adding several timestamp
> columns.  I would like to query the table for those rows for which the
> timestamp has not been set but am unable to find a way to do so.  any
> tips ?  in psql the column appears empty and char_length returns
> nothing.
> 
> Some of what I have tried 
> 
> "" -> not valid
> 
> char_length(last_timestamp) > 0  -> returns the rows with the time
> stamps
> 
> where NOT char_length(last_timestamp) > 0  -> returns 0 rows
> 
> where NOT (char_length(last_timestamp) > 0 ) -> returns 0 rows
> 
> where last_timestamp = NULL;  -> 0 rows
> 
> this is kicking my butt, and starting to piss me off so if any one has
> tips I would appreciate it.
> 
> Bret
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 
> !DSPAM:421b9fba271461201672223!
> 
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Speeds using a transaction vrs not

[Joel Fradkin]

I wrote a .net program to move my data to postgres (works great on
SQ_ASCII).

In fiddling around I tried it using the odbc driver and a transaction
originally, but converted it to using the .net connectivity but no
transaction.

What I found was it moved my database (4 gig in MSSQL) in 2 hours using the
.net, but 12 hours with the odbc and transaction.

Have any of you played around to see if using a transaction should be that
much slower or is it the odbc versus .net?

I paid a consultant to look at what would work best and his speed tests
indicated the odbc and the .net were pretty close, so I am assuming it is
because I am using a transaction on my odbc test.
I can run again without it or with it on .net driver, but thought I would
ask. 
We only use transaction on important multiple table updates in our system
now, so should not be a huge thing, but was curious.

Joel Fradkin
 



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Working with XML.

[Theo Galanakis]

Title: Message



George,
 
    I have run this SP in Cold 
Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same results. We are 
currently using Redhat ES3 and Postgres 7.4.5.
 
Theo
  -Original 
Message-From: George Weaver [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 23 February 2005 12:46 AMTo: Theo 
GalanakisCc: pgsql-sql@postgresql.orgSubject: Re: [SQL] 
Working with XML.

  Hi Theo,
   
  I am not able to duplicate the problem you 
  experienced.  I tried the query you provided below using pgAdmin and 
  psql on a 7.3.2 and an 8.0 database with success.  I then copied all the 
  rows and pasted them to the end so that I would have 100 rows, and 
  the query worked as expected.
   
  Perhaps the client you're using is causing the 
  problem.  Can you run the query in pgAdmin?
   
  Regards,
  George
  
- Original Message - 
From: 
Theo Galanakis 
To: 'George Weaver' 
Cc: 'pgsql-sql@postgresql.org' 

Sent: Monday, February 21, 2005 11:54 
PM
Subject: RE: [SQL] Working with 
XML.

Thankyou George,
 
XML2 compiled ok... next 
stummbling block..  when I pass a very long XML string 
to xpath_string() it crashes the postgres server. Client receives a 
message like:
 
server closed the connection unexpectedlyThis 
probably means the server terminated abnormallybefore or while 
processing the request.
 
Below is the function call, a smaller xml string 
with say 40 records works fine, anything bigger crashes the 
server.
 
select xpath_string(' 7741872226632005-02-22 


__
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.

[SQL] Pagination with Output Variables?

[Steve - DND]

I have a MSSQL Stored Procedure that returns a particular section of a
resultset depending on the desired "page". This procedure also has a
parameter marked as OUTPUT which indicates the total number of records in
the actual resultset. Is there a way to do something similar in PG or does
TotalRecords need to be part of the type returned by the function.

MSSQL Procedure

CREATE PROCEDURE PageCustomers (
@Page int,
@PerPage int,
@TotalRecords int OUTPUT
) AS

--Do stuff to determine number of records
SELECT @TotalRecords = COUNT(*) FROM tmpCustomers

--Get the actual data
SELECT CustomerID, CustomerName, EmailAddress FROM tmpCustomers WHERE ...


Would the only way to do this in PG to have my function return a type like
this?
CREATE TYPE "PageCustomers_type"
   AS ("TotalRecords" int4, "CustomerID" int4, "CustomerName" varchar(100),
"EmailAddress" varchar(100));

Can a function return two type results? So that the first type would be just
the total number of records, and the second type would be the resultset of
customers?

Thanks,
Steve



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Pagination with Output Variables?

[Michael Fuhr]

On Tue, Feb 22, 2005 at 04:29:30PM -0700, Steve - DND wrote:

> Can a function return two type results? So that the first type would be just
> the total number of records, and the second type would be the resultset of
> customers?

The function could return a set of cursors, one for the query that
returns the total number of records and one for the resultset.  The
8.0 documentation has an example that should work in earlier versions
as well (except for the dollar quoting):

http://www.postgresql.org/docs/8.0/static/plpgsql-cursors.html

See the example at the bottom of the page.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] FW: Working with XML.

[Mirko Zeibig]

Theo Galanakis wrote:
Hi, 
I have copied all the files manually from http

://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution.
However I have the following issue when I attempt to compile with gmake:
 
gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include -D_GNU_SOURCE   -c
-o xpath.o xpath.c
xpath.c: In function `xpath_table':
xpath.c:689: `work_mem' undeclared (first use in this function)
xpath.c:689: (Each undeclared identifier is reported only once
xpath.c:689: for each function it appears in.)
gmake: *** [xpath.o] Error 1
 
I have installed :
 
libxml2-devel-2.5.10-1.rpm
 
What am I doing wrong, or can someone point me to the direction of a binary
for XML2 on RedHat ES3, Postgres 7.4.5.
Hello Theo,
this has nothing to do with libxml2. `work_mem` is a variable declared 
in one of the PostgreSQL include files. The `-I../../src/include` 
directive in the compile statement above should point to the include-dir 
of your PostgreSQL installation.

Regards
Mirko
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] Trigger

[Eugen Gass]

Hi,
Thank you ALL for the fast help

it works fine with following code:

CREATE OR REPLACE FUNCTION synchronize () RETURNS trigger AS 'BEGIN
NEW.objectid := NEW.gid; RETURN NEW; END;' LANGUAGE plpgsql;

CREATE TRIGGER syncl_holz_lager AFTER INSERT ON holz_lagerplatz FOR EACH ROW
EXECUTE PROCEDURE synchronize();

Best Regards

Eugen




-Ursprüngliche Nachricht-
Von: Pavel Rabel [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 16. Februar 2005 21:43
An: Eugen Gass
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] Trigger


It requires a bit more work in PostgreSQL to create a trigger.

 From the documentation: "It is not currently possible to write a
SQL-language trigger function. Trigger functions can be written in C or
in some of the available procedural languages."

I guess you will prefer to write the trigger in PL/pgSQL, have a look at
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html

Regards

Pavel

Eugen Gass wrote:

>Hi,
>
>I'm trying to create a trigger on PostgreSQL
>
>it should be like an oracle(sql) sample code:
>
>create or replace trigger frei_polygon_sync
>after INSERT on frei_polygon
>Referencing NEW as newROW
>for each row
>Begin
> :newRow.objektid :=  :newRow.gid;
> :
>end;
>
>Can sombody help me to do the same on Postrgres
>
>Thanks
>
>Best Regards
>
>EG
>
>---(end of broadcast)---
>TIP 5: Have you checked our extensive FAQ?
>
>   http://www.postgresql.org/docs/faq
>
>
>


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Comments on subquery performance

[T- Bone]

Hello all,
I created a query that contains two subqueries and joins and would like some 
feedback on whether:
 1) this approach is logical; and,
 2) if this is an optimal approach (performance wise) to return the records 
I seek.

Essentially, I am attempting to perform a 'lookup' on a value in another 
table 3 times, for three different columns.  I have three columns with 
category codes in tblListings and would like to 'lookup' the actual category 
text in tblCategory.  I have created a functional query that contains two 
subqueries and joins, but am concerned this may not be the fastest (or 
logical?) way to achieve what I seek.

I thought of another approach to create a function to evaluate the records 
on a row-by-row and column-by-column approach, but thought that may prove 
even slower.  I would appreciate any comments on my logic or learning of any 
alternative means that would result in better performance.

I have included the SQL for reference.  Thanks in advance.
Regards,
Jim
8<-
SELECT "CatID1", "CatID2", "CatID3", c1 AS "CatName1", c2 AS "CatName2", 
t6."CatName" AS "CatName3"
	FROM
	(SELECT "CatID1", "CatID2", "CatID3", c1, t4."CatName" AS c2
		FROM
	  (SELECT t1."CatID1", t1."CatID2", t1."CatID3", t2."CatName" AS c1
			FROM "MySchema"."tblListings" t1
			INNER JOIN  "MySchema"."tblCategories" t2
			ON (t1."CatID1" = t2."CatID")) t3
	  	LEFT OUTER JOIN "MySchema"."tblCategories" t4
	  	ON (t3."CatID2" = t4."CatID")) t5
  LEFT OUTER JOIN "MySchema"."tblCategories" t6
  ON (t5."CatID3" = t6."CatID");

8<-
_
MSN® Calendar keeps you organized and takes the effort out of scheduling 
get-togethers. 
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines 
 Start enjoying all the benefits of MSN® Premium right now and get the 
first two months FREE*.

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] VIEW / ORDER BY + UNION

[WeiShang]

Hi, I have created a view like this :

CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
t1.orderno=t2.orderno);

if I create a SQL statment:

(SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
UNION
(SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
UNION
(SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);

Will the whole result will be sorted by the field orderno?

Thanks,
WeiShang












---(end of broadcast)---
TIP 3: 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: [SQL] VIEW / ORDER BY + UNION

[CoL]

hi,
WeiShang wrote, On 2/17/2005 16:46:
Hi, I have created a view like this :
CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
t1.orderno=t2.orderno);
if I create a SQL statment:
(SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
UNION
(SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
UNION
(SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);
Will the whole result will be sorted by the field orderno?
nothing says it will. you havet to sort the set of unions.
(select orderno ) union (select orderno ) order by orderno.
Not necessary now (no sense), to use order by in selects inside union.
C.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Trigger

[Pavel Rabel]

It requires a bit more work in PostgreSQL to create a trigger.
From the documentation: "It is not currently possible to write a 
SQL-language trigger function. Trigger functions can be written in C or 
in some of the available procedural languages."

I guess you will prefer to write the trigger in PL/pgSQL, have a look at 
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html

Regards
Pavel
Eugen Gass wrote:
Hi,
I'm trying to create a trigger on PostgreSQL
it should be like an oracle(sql) sample code:
create or replace trigger frei_polygon_sync
after INSERT on frei_polygon
Referencing NEW as newROW
for each row
Begin
:newRow.objektid :=  :newRow.gid;
:
end;
Can sombody help me to do the same on Postrgres
Thanks 

Best Regards
EG
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq
 


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match