[GENERAL] how to create aggregate xml document in 8.3?

[Matt Magoffin]

Hello, I'm trying to write a query to return an XML document like


  
  
  ...


I started with

select xmlelement(name range, xmlattributes(m.range, count(s.id) as "count"))
from mb_sale s
inner join mb_lead m on m.sale_id = s.id
where
s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30')
and s.sale_type = 'd' -- direct sale
group by m.range
order by m.range;

 xmlelement

 
 
 
 
(4 rows)

which returns 4 individual rows as shown, but I can't figure out how to
correctly produce this with a root element and the  elements nested
under that.

I tried a variety of ways, including

select xmlelement(name "matchback-months",
xmlattributes('bar' as "foo"),
xmlagg(
xmlelement(name "range",
xmlattributes(m.range, count(s.id) as "count")
)
)
)
from mb_sale s
inner join mb_lead m on m.sale_id = s.id
where
s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30')
and s.sale_type = 'd'
group by m.range
order by m.range;

which returns an error "aggregate function calls cannot be nested".

Is this type of output possible in 8.3?

-- m@





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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Trevor Talbot]

On 12/10/07, Colin Wetherbee <[EMAIL PROTECTED]> wrote:
> Vivek Khera wrote:
> > On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:

> >> IF (a query matching your old data returns rows) THEN UPDATE with
> >> your new data ELSE INSERT your new data

> > Still exists race condition.  Your race comes from testing existence,
> >  then creating/modifying data afterwards.  You need to make the
> > test/set atomic else you have race.

> I guess when I wrote that the algorithm would have to be implemented in
> an atomic manner, it fell on deaf ears.

The problem is that there isn't a good atomic method for that order of
operations, short of locking the entire table first. A concurrent
transaction might insert a row after your test but before your own
INSERT. Even a SERIALIZABLE transaction won't help, as PostgreSQL
doesn't implement predicate locking.

That's why the example in the docs is a loop with result checking on
both operations, and requires a UNIQUE constraint to work correctly.

If high concurrency isn't a concern, table locking is the simpler approach.

---(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] Restore problem

[Trevor Talbot]

On 12/10/07, Keith Turner <[EMAIL PROTECTED]> wrote:

> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on
> someone else's network, though we do have anti-virus running, the
> symptoms were worrying - so we had to wipe and reinstall the OS and all
> programs. We had recovered the 8.1 folder and the data off the crashed
> computer prior to the wipe, but the last good .backup file is a few days
> older than that.
>
> Are there step by step instructions on restoring from the folder itself
> instead of a backup file? Is it even possible?

I would try installing 8.1 again, stop the service, _copy_ your data
and config over the top of it, then start it again and see what
happens. Any problems with that should show up immediately in the
logs.

And if all else fails, you can just nuke the attempt and restore from
the older backups.

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

Re: [GENERAL] Restore problem

[A. Kretschmer]

am  Mon, dem 10.12.2007, um 12:30:14 -0800 mailte Keith Turner folgendes:
> Hi first post here, I hope you can help.
> 
> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on

Please don't hijack other threads, the original thread was 'TIMESTAMP
difference'.

(don't answer to an arbitrary other mail and change the subject. Every
mail contains references-header)



> Are there step by step instructions on restoring from the folder itself
> instead of a backup file? Is it even possible?

not really...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(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] question about warm standby databases in 8.2.5

[Greg Smith]

On Mon, 10 Dec 2007, Brett Neumeier wrote:


It seems that the recovery command always copies the source WAL file (with a
name like 00010002009C) to a file path "pg_xlog/RECOVERYXLOG",
which is fine. However, then when we abort recovery, postgresql seems to
expect that the most recent WAL log should be in pg_xlog with its original
filename, e.g., the 09C filename from above.


I think your expectation for what the recovery command script you provide 
does and what the server actually requires are a little mismatched. 
RECOVERYXLOG is strictly a temporary file and as you've discovered the 
server may want the original back again by its original name.  This has 
come up before--check out this thread, from this message to the end:


http://archives.postgresql.org/pgsql-admin/2007-08/msg00425.php

It's pointed out there that the documentation is little sparse in this 
area.


Not that many people run into this because there is a reference 
implementation of a recovery command that correctly implements the 
required behavior:


http://developer.postgresql.org/pgdocs/postgres/pgstandby.html

pg_standby ships with the upcoming 8.3, but if you grab that code you can 
run it just fine against an 8.2 system.  You'd be better off in the long 
run (and probably the short run too) replacing whatever script you've 
developed with that one, so you can just get updates to it rather than 
keeping one updated in-house.  Consider the time you've spend working on 
your own not wasted but educational--you can never know too much about 
disaster recovery of your database.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] partitioned table query question

[Tom Lane]

"Trevor Talbot" <[EMAIL PROTECTED]> writes:
> Erik is questioning is why it has to assume anything. Why can't it
> just execute the expression and find out?

Because the whole point of the problem is to *not* execute the
expression, but to assume that it must yield false, for every row
of a given partition.  Without a solid logical basis for that
assumption, you're just building a house of cards.

The bottom line here is that we have built a partitioning facility
out of spare parts, ie, a very generalized contradiction-proving
section of the planner.  It's been an interesting exercise, and
it's certainly resulted in a better contradiction-prover than
we would have had otherwise, but it's got obvious limitations both
in planning performance and in the sorts of partitioning rules we
can support.  My feeling is that trying to push the current approach to
do bin or hash partitioning transparently is likely not reasonable.
Eventually we'll have to push an understanding of partitioning down to
some lower level of the system --- that is, if we think it's critical
enough to justify that much effort.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] SQL design pattern for a delta trigger?

[Colin Wetherbee]

Vivek Khera wrote:

On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:

For what it's worth, the real algorithm would be as follows.  I 
hadn't had enough coffee yet, and I forgot the UPDATE bit.


IF (a query matching your old data returns rows) THEN UPDATE with 
your new data ELSE INSERT your new data


Still exists race condition.  Your race comes from testing existence,
 then creating/modifying data afterwards.  You need to make the 
test/set atomic else you have race.


I guess when I wrote that the algorithm would have to be implemented in
an atomic manner, it fell on deaf ears.

That said, perhaps implementing a good MERGE would be not such a bad
idea for PostgreSQL 8.4.

Colin


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

Re: [GENERAL] partitioned table query question

[Trevor Talbot]

On 12/10/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 12/10/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> > Erik Jones <[EMAIL PROTECTED]> writes:
> > > I guess what I don't understand is that given the query
> >
> > > SELECT COUNT(*)
> > > FROM table
> > > WHERE some_id=34;
> >
> > > on a table with the much discussed constraint (34 % 100) = 32 isn't
> > > simply evaluated as a one-time filter whenever whatever constraint
> > > exclusion code examines child partition tables' constraints.
> >
> > I'm not sure how else to explain it: the fact that the WHERE clause
> > asserts that some operator named "=" will succeed on some_id and 34
> > is not sufficient grounds to assume that "some_id % 100" and "34 % 100"
> > will give the same result.  Knowing that the "=" operator is a btree
> > equality operator gives us latitude to make certain conclusions, but
> > not that one, because there is no way to know whether the semantics
> > of the particular btree operator class have anything to do with the
> > behavior of "%".
>
> Erik is questioning is why it has to assume anything. Why can't it
> just execute the expression and find out? On a high level, the
> partitioning system looks exactly like partial expression indexes.

...Oops. I sit here for 10 minutes pondering it, and figure out the
comparison with expression indexes isn't really true 2 seconds after I
hit "send". Sigh.

---(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] partitioned table query question

[Trevor Talbot]

On 12/10/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Erik Jones <[EMAIL PROTECTED]> writes:
> > I guess what I don't understand is that given the query
>
> > SELECT COUNT(*)
> > FROM table
> > WHERE some_id=34;
>
> > on a table with the much discussed constraint (34 % 100) = 32 isn't
> > simply evaluated as a one-time filter whenever whatever constraint
> > exclusion code examines child partition tables' constraints.
>
> I'm not sure how else to explain it: the fact that the WHERE clause
> asserts that some operator named "=" will succeed on some_id and 34
> is not sufficient grounds to assume that "some_id % 100" and "34 % 100"
> will give the same result.  Knowing that the "=" operator is a btree
> equality operator gives us latitude to make certain conclusions, but
> not that one, because there is no way to know whether the semantics
> of the particular btree operator class have anything to do with the
> behavior of "%".

Erik is questioning is why it has to assume anything. Why can't it
just execute the expression and find out? On a high level, the
partitioning system looks exactly like partial expression indexes.

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

   http://archives.postgresql.org/

[GENERAL] question about warm standby databases in 8.2.5

[Brett Neumeier]

Hi,

I set up a warm standby failover system on Redhat, using built-from-source
postgresql 8.2.5 on (of course) both the master and standby systems.

The setup of the system was very easy, and the recovery script we have in
place on the standby system correctly copies in the archived WAL log files,
which are then applied.

What seems odd is what happens when we abort the continuous recovery so the
standby database becomes primary.

It seems that the recovery command always copies the source WAL file (with a
name like 00010002009C) to a file path "pg_xlog/RECOVERYXLOG",
which is fine. However, then when we abort recovery, postgresql seems to
expect that the most recent WAL log should be in pg_xlog with its original
filename, e.g., the 09C filename from above.

This seems broken -- if the WAL file should wind up in the pg_xlog directory
with the 0...9C name, why isn't postgresql copying it there?

Here are the log messages that show what I'm talking about. Note that
everything is fine for quite a while; then we triggered the standby database
to come online before 0...B4 was archived...and postgresql then bails out
because 0...B3 (which has already been restored) doesn't exist!

We're working around this, for now, by having the recovery command script
copy archived WAL files to the specified location pg_xlog/RECOVERYXLOG, and
also to the pg_xlog directory with the file's original basename. But that
seems awfully sloppy, and isn't the process documented in the manual.

Advice is eagerly solicited!

LOG:  starting archive recovery
LOG:  restore_command = "/home/pgsql/bin/recover_script.rb %f %p"
LOG:  restored log file "0001000200A1.001FAD68.backup" from
archive
LOG:  restored log file "0001000200A1" from archive
LOG:  checkpoint record is at 2/A11FAD68
LOG:  redo record is at 2/A11FAD68; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 0/82464990; next OID: 45282
LOG:  next MultiXactId: 28; next MultiXactOffset: 55
LOG:  automatic recovery in progress
LOG:  redo starts at 2/A11FADB0
LOG:  restored log file "0001000200A2" from archive
[a bunch of similar messages omitted]
LOG:  restored log file "0001000200B3" from archive
LOG:  could not open file "pg_xlog/0001000200B4" (log file 2,
segment 180): No such file or directory
LOG:  redo done at 2/B354BDD0
PANIC:  could not open file "pg_xlog/0001000200B3" (log file 2,
segment 179): No such file or directory
LOG:  startup process (PID 17604) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time
2007-12-10 16:57:42 EST
HINT:  If this has occurred more than once some data may be corrupted and
you may need to choose an earlier recovery target.

Cheers,

bn

-- 
Brett Neumeier ([EMAIL PROTECTED])

Re: [GENERAL] partitioned table query question

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> I guess what I don't understand is that given the query

> SELECT COUNT(*)
> FROM table
> WHERE some_id=34;

> on a table with the much discussed constraint (34 % 100) = 32 isn't  
> simply evaluated as a one-time filter whenever whatever constraint  
> exclusion code examines child partition tables' constraints.

I'm not sure how else to explain it: the fact that the WHERE clause
asserts that some operator named "=" will succeed on some_id and 34
is not sufficient grounds to assume that "some_id % 100" and "34 % 100"
will give the same result.  Knowing that the "=" operator is a btree
equality operator gives us latitude to make certain conclusions, but
not that one, because there is no way to know whether the semantics
of the particular btree operator class have anything to do with the
behavior of "%".

If you dig in the PG archives you will find some discussions of
inventing a "real equality" flag for operators, which would authorize
the planner to make such deductions for any immutable operator/function.
The idea hasn't gone anywhere, partly because it's not clear that it
would really help in very many common cases.  The fact that we could
*not* set the flag on such common cases as float and numeric equality
is a bit discouraging in that connection.

regards, tom lane

---(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] SQL design pattern for a delta trigger?

[Alvaro Herrera]

Richard Broersma Jr wrote:
> --- On Mon, 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
> 
> > but how do you
> > do it using SQL in an RDBMS?  
> 
> I believe that there is an ANSI SQL command "MERGE" that is yet to be 
> implemented into PostgreSQL.

IIRC the standard's definition of MERGE is still subject to the race
condition :-)  It seems mostly defined for OLAP, and assumes rather
static data.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"La tristeza es un muro entre dos jardines" (Khalil Gibran)

---(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] slony question

[Geoffrey]

Scott Marlowe wrote:

On Dec 10, 2007 2:19 PM, Chris Browne <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] ("Josh Harrison") writes:

Does slony support postgres major version upgrade ? ie., will it
replicate between different major versions?

Yes, that's one of the major "use cases" for Slony-I.

Version 1.0 and 1.1 support (loosely) from PostgreSQL 7.3 to 8.1-ish.

Version 1.2 dropped 7.3 support, but supports up to (just about;
there's a patch not in a 1.2 release that is needed for late-breaking
8.3 changes) 8.3.


Didn't 1.2 drop support for pg 7.4 as well?  I thought I remembered
reading that some time ago.


Not according to the docs:

http://slony.info/documentation/requirements.html
Section 3.1, 3rd bullet:

You also need a recent version of PostgreSQL source. Slony-I depends on 
namespace support so you must have PostgreSQL version 7.3.3 or newer to 
be able to build and use Slony-I.


It says the same thing in the Slony 1.2.12 docs rpm.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

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

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

Re: [GENERAL] partitioned table query question

[Erik Jones]

On Dec 10, 2007, at 5:50 PM, Tom Lane wrote:


Erik Jones <[EMAIL PROTECTED]> writes:

Forgive me if I'm nagging on this, I just want to understand this
better.  Why does evaluating a CHECK constraint like 'CHECK some_id %
100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know
anything about equality properites of %?  Or, rather, why does it
stop there?  Can't it just substitute the given value for some_id in
to the check expression, execute it and check the result value for
TRUE/FALSE?


What "given value"?


The where clause in the select query.


What you're missing is that the condition "a = b" does not mean that
"f(a) = f(b)" for every function f.  It is possible to define
constraints on equality that would make that true, but such  
constraints
would be far stronger than what is required to make btree (or even  
hash)

indexes work.


In the table constraint CHECK (some_id % 100 = 32), isn't that just f 
(a) = 32?



In the example I gave, we are able to conclude that 3 is unequal to 9
not because of any a-priori knowledge, but because we apply the  
specific

operator to the specific constants and find out that it yields false.
Our knowledge of the consistency requirements that are imposed on  
btree

equality operators then allows us to determine that the two original
conditions can't be true at the same time.

This does *not* imply assuming that the two constants are really "the
same" in the sense that no other operator in the system could tell  
them

apart.  This isn't mere academic hairsplitting: there actually are
standard equality operators in the system for which such a conclusion
would fail.  I already mentioned float comparison, and numeric
comparison has similar behaviors --- for instance,

regression=# select '0.00'::numeric = '0.0'::numeric;
 ?column?
--
 t
(1 row)

regression=# select text('0.00'::numeric) = text('0.0'::numeric);
 ?column?
--
 f
(1 row)


I get your arguments wrt the known (to the planner) semantics of the  
equality operator.  I guess what I don't understand is that given the  
query


SELECT COUNT(*)
FROM table
WHERE some_id=34;

on a table with the much discussed constraint (34 % 100) = 32 isn't  
simply evaluated as a one-time filter whenever whatever constraint  
exclusion code examines child partition tables' constraints.


Again, though, is there some better way to go about implementing some  
kind of hash based partitioning in postgres besides this that would  
be more natural wrt queries?


Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

  http://archives.postgresql.org/

Re: [GENERAL] partitioned table query question

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> Forgive me if I'm nagging on this, I just want to understand this  
> better.  Why does evaluating a CHECK constraint like 'CHECK some_id %  
> 100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know  
> anything about equality properites of %?  Or, rather, why does it  
> stop there?  Can't it just substitute the given value for some_id in  
> to the check expression, execute it and check the result value for  
> TRUE/FALSE?

What "given value"?

What you're missing is that the condition "a = b" does not mean that
"f(a) = f(b)" for every function f.  It is possible to define
constraints on equality that would make that true, but such constraints
would be far stronger than what is required to make btree (or even hash)
indexes work.

In the example I gave, we are able to conclude that 3 is unequal to 9
not because of any a-priori knowledge, but because we apply the specific
operator to the specific constants and find out that it yields false.
Our knowledge of the consistency requirements that are imposed on btree
equality operators then allows us to determine that the two original
conditions can't be true at the same time.

This does *not* imply assuming that the two constants are really "the
same" in the sense that no other operator in the system could tell them
apart.  This isn't mere academic hairsplitting: there actually are
standard equality operators in the system for which such a conclusion
would fail.  I already mentioned float comparison, and numeric
comparison has similar behaviors --- for instance,

regression=# select '0.00'::numeric = '0.0'::numeric;
 ?column? 
--
 t
(1 row)

regression=# select text('0.00'::numeric) = text('0.0'::numeric); 
 ?column? 
--
 f
(1 row)

regards, tom lane

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

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

Re: [GENERAL] Unable to ALTER table after SELECT data from table

[Keary Suska]

on 12/10/07 2:27 PM, [EMAIL PROTECTED] purportedly said:

>>> To work around this I add an dbCon.rollBack() after select statement
>>> above in good and in bad times. After that ALTER works.
>>> Can someone explain me why I need this rollback ?
>> 
>> You only need the rollback when the SELECT statement fails. When an error
>> occurs within a transaction, the transaction is aborted but not rolled back.
>> There are likely reasons why the rollback isn't automatic, but I don't know
>> them.
> 
> I think you confuse postgresql with some other database.  Without
> setting a savepoint, any error during a transaction and roll it back
> upon connection close | rollback | commit.
> 
> I.e. the rollback IS automatic.  Just not necessarily immediate.

I suppose this clarification is useful for posterity, but or the record, I
as using the term "automatic" to address the apparent expectations of the
OP, and not to the ultimate behavior of postgresql

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"



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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Richard Broersma Jr]

--- On Mon, 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:

> but how do you
> do it using SQL in an RDBMS?  

I believe that there is an ANSI SQL command "MERGE" that is yet to be 
implemented into PostgreSQL.

Regards,
Richard Broersma Jr.

---(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] partitioned table query question

[Erik Jones]

On Dec 10, 2007, at 4:29 PM, Tom Lane wrote:


Erik Jones <[EMAIL PROTECTED]> writes:

You beat me to the punch on this one.  I was wanting to use modulo
operations for bin style partitioning as well, but this makes things
pretty awkward as well as unintuitive.  So, to the postgres gurus:
What are the limitations of check constraints when used with
constraint exclusion?  Is this really the intended behavior?


Don't hold your breath.  predtest.c has some intelligence about
btree-indexable comparison operators, but none about modulo.

In the particular case here, the reason that
WHERE (foo % 10) = 3
is seen to be incompatible with a check constraint
(foo % 10) = 9
is that the "=" is btree indexable, so predtest knows something about
its semantics; and given that % is an immutable operator, the code is
able to see that these could only both be true if 3 = 9.


I get that.


This
deduction involves exactly zero %-specific knowledge.  In particular
it doesn't require assuming that "a=b" implies "(a % c) = (b % c)",
which would involve much more knowledge about the specific operators
involved than is available to the planner.  (The fact that an operator
is a btree equality member doesn't mean that it might not consider two
values to be equal that are distinct to some other operators of the
data type.  See plus and minus zero in IEEE float arithmetic for one
handy example ... and that's not even considering nonstandard versions
of equality.)


Forgive me if I'm nagging on this, I just want to understand this  
better.  Why does evaluating a CHECK constraint like 'CHECK some_id %  
100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know  
anything about equality properites of %?  Or, rather, why does it  
stop there?  Can't it just substitute the given value for some_id in  
to the check expression, execute it and check the result value for  
TRUE/FALSE?


On a related note, how would you recommend implementing some kind of  
bin based (i.e. hash values, round robin, etc...) partitioning scheme  
if this won't work?  I've tried a number of different approaches with  
functions in the check constraint but can't seem to get anything  
going there either.  I'm the third person this week (all in this  
thread and another I had going) that's interested in this approach.


Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Ted Byers]

Thanks Erik
> 
> In a stored procedure you'd just execute the UPDATE
> and then check  
> the FOUND variable to see if it found a row to
> update:
> 
> UPDATE table_name SET foo='bar' WHERE id=5;
> 
> IF NOT FOUND THEN
>   INSERT INTO table_name (id, foo) VALUES (5, 'bar');
> END IF;
> 
To be clear, if I understand you correctly, with your
example, if there is no record where id=5, nothing
happens except FOUND is set to false?  Can I, then,
declare a variable prior to your update statement, and
then modify your update statement so that the value in
a particular field on the row where id=5 can be
captured?  Bearing in mind this is to be in a row
level trigger after an insert into table_name,
something like:

DECLARE q DOUBLE;
UPDATE  table_name 
   SET foo='bar',
   q = table_name.quantity 
 WHERE id=5;

And then follow that with something like:
IF FOUND THEN
  INSERT INTO another_table (baz,quantity)
VALUES (foo,q+NEW.quantity);
ELSE
  INSERT INTO another_table (baz,quantity)
VALUES (foo,NEW.quantity);
END IF

Thanks again,

Ted

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

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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Erik Jones]

On Dec 10, 2007, at 4:48 PM, Ted Byers wrote:



--- Vivek Khera <[EMAIL PROTECTED]> wrote:



On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:


For what it's worth, the real algorithm would be

as follows.  I

hadn't had enough coffee yet, and I forgot the

UPDATE bit.


IF
 (a query matching your old data returns rows)
THEN
 UPDATE with your new data
ELSE
 INSERT your new data


Still exists race condition.  Your race comes from
testing existence,
then creating/modifying data afterwards.  You need
to make the test/
set atomic else you have race.



Yes, but how do you do that in a stored function or
procedure or in a trigger.  It would be obvious to me
if I were writing this in C++ or Java, but how do you
do it using SQL in an RDBMS?

I saw something about table locks, but that doesn't
seem wise, WRT performance.

The classic example of a race condition, involving a
bank account, was used in the manual to introduce the
idea of a transaction, but we can't use a transaction
in a trigger, can we?

It is one thing to point out a race condition, but a
pointer to a solution that would work in the context
of the problem at hand would be useful and
appreciated.

Thanks all.


In a stored procedure you'd just execute the UPDATE and then check  
the FOUND variable to see if it found a row to update:


UPDATE table_name SET foo='bar' WHERE id=5;

IF NOT FOUND THEN
INSERT INTO table_name (id, foo) VALUES (5, 'bar');
END IF;

Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Ted Byers]

--- Vivek Khera <[EMAIL PROTECTED]> wrote:

> 
> On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
> 
> > For what it's worth, the real algorithm would be
> as follows.  I  
> > hadn't had enough coffee yet, and I forgot the
> UPDATE bit.
> >
> > IF
> >  (a query matching your old data returns rows)
> > THEN
> >  UPDATE with your new data
> > ELSE
> >  INSERT your new data
> 
> Still exists race condition.  Your race comes from
> testing existence,  
> then creating/modifying data afterwards.  You need
> to make the test/ 
> set atomic else you have race.
> 

Yes, but how do you do that in a stored function or
procedure or in a trigger.  It would be obvious to me
if I were writing this in C++ or Java, but how do you
do it using SQL in an RDBMS?  

I saw something about table locks, but that doesn't
seem wise, WRT performance.

The classic example of a race condition, involving a
bank account, was used in the manual to introduce the
idea of a transaction, but we can't use a transaction
in a trigger, can we?

It is one thing to point out a race condition, but a
pointer to a solution that would work in the context
of the problem at hand would be useful and
appreciated.

Thanks all.

Ted

---(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] partitioned table query question

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> You beat me to the punch on this one.  I was wanting to use modulo  
> operations for bin style partitioning as well, but this makes things  
> pretty awkward as well as unintuitive.  So, to the postgres gurus:
> What are the limitations of check constraints when used with  
> constraint exclusion?  Is this really the intended behavior?

Don't hold your breath.  predtest.c has some intelligence about
btree-indexable comparison operators, but none about modulo.

In the particular case here, the reason that
WHERE (foo % 10) = 3
is seen to be incompatible with a check constraint
(foo % 10) = 9
is that the "=" is btree indexable, so predtest knows something about
its semantics; and given that % is an immutable operator, the code is
able to see that these could only both be true if 3 = 9.  This
deduction involves exactly zero %-specific knowledge.  In particular
it doesn't require assuming that "a=b" implies "(a % c) = (b % c)",
which would involve much more knowledge about the specific operators
involved than is available to the planner.  (The fact that an operator
is a btree equality member doesn't mean that it might not consider two
values to be equal that are distinct to some other operators of the
data type.  See plus and minus zero in IEEE float arithmetic for one
handy example ... and that's not even considering nonstandard versions
of equality.)

regards, tom lane

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

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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Vivek Khera]

On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:

For what it's worth, the real algorithm would be as follows.  I  
hadn't had enough coffee yet, and I forgot the UPDATE bit.


IF
 (a query matching your old data returns rows)
THEN
 UPDATE with your new data
ELSE
 INSERT your new data


Still exists race condition.  Your race comes from testing existence,  
then creating/modifying data afterwards.  You need to make the test/ 
set atomic else you have race.



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

Re: [GENERAL] Script to reset all sequence values in the a given DB?

[Vivek Khera]

please don't hijack old threads ("partitioned table query question" in  
this case) and change the subject line to start your new question. it  
messes up threaded mail readers.


thanks.


On Dec 10, 2007, at 3:00 PM, Nathan Wilhelmi wrote:

Hello - Does anyone happen to have a SQL script or function that can  
reset all the sequence values found in a given DB? When we rebuild  
the DB it would be handy to be able to set all the sequence back to  
a known starting place.


Thanks!

-Nate


---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings



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

Re: [GENERAL] SQL design pattern for a delta trigger?

[Colin Wetherbee]

Vivek Khera wrote:

On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:

You can do this with a conditional.  Something like the following 
should work.


IF
 NOT (a query matching your data returns rows)
THEN
 INSERT (your new data)


There exists a race condition here unless you've locked your tables.


Yes, clearly.  In the context of the thread, I was assuming my algorithm 
would be implemented as an atomic transaction.


For what it's worth, the real algorithm would be as follows.  I hadn't 
had enough coffee yet, and I forgot the UPDATE bit.


IF
  (a query matching your old data returns rows)
THEN
  UPDATE with your new data
ELSE
  INSERT your new data

Colin

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

  http://archives.postgresql.org/

Re: [GENERAL] slony question

[Scott Marlowe]

On Dec 10, 2007 2:19 PM, Chris Browne <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] ("Josh Harrison") writes:
> > Does slony support postgres major version upgrade ? ie., will it
> > replicate between different major versions?
>
> Yes, that's one of the major "use cases" for Slony-I.
>
> Version 1.0 and 1.1 support (loosely) from PostgreSQL 7.3 to 8.1-ish.
>
> Version 1.2 dropped 7.3 support, but supports up to (just about;
> there's a patch not in a 1.2 release that is needed for late-breaking
> 8.3 changes) 8.3.

Didn't 1.2 drop support for pg 7.4 as well?  I thought I remembered
reading that some time ago.

---(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] comparing rows

[Reece Hart]

On Mon, 2007-12-10 at 13:31 -0800, hjenkins wrote:

> I would like to take a timeseries of data and extract the rows of data
> flanking the gaps in it. So I need to compare timestamps from two
> adjacent
> rows, and determine if the interval is greater than the standard
> sampling
> interval.


It often helps for us to have a snippet of a table definition to frame
replies.  I'll assume that you have a "data" table with a timestamp
column called "ts". I suspect you could use a subquery, like this:

=> select D1.ts as ts1,(select ts from data D2 where D2.ts>D1.ts limit
1) as ts2 from data D1;

I'm uncertain about the performance of this subquery in modern PGs.  If
this query works for you, then you can wrap the whole thing in a view or
another subquery in order to compute ts2-ts1, like this:

=> select ts1,ts2,ts2-ts1 as delta from (  ) X;


This will get you only the timestamps of adjacent rows with large
deltas. The easiest way to get the associated data is to join on the
original data table where ts1=ts or ts2=ts.


-Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Script to reset all sequence values in the a given DB?

[Obe, Regina]

 Did you want to set to a specific known value or the min value of the
sequence.  I think Pavel's sets to the min value of the sequence.

The below sets all the sequences to the same value

CREATE AGGREGATE sum ( BASETYPE = text,
  SFUNC = textcat,
STYPE = text,
INITCOND = '' );


CREATE OR REPLACE FUNCTION cp_resetsequences(resetto integer)
  RETURNS void AS
$BODY$
BEGIN
EXECUTE (SELECT SUM('ALTER SEQUENCE ' || sequence_schema || '.'
|| sequence_name || ' RESTART WITH ' || CAST(resetto As varchar(50)) ||
'; ' ) 
FROM  information_schema.sequences);
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


--Note this will set all the sequences in the database to 150
 SELECT cp_resetsequences(150);



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Pavel Stehule
Sent: Monday, December 10, 2007 4:33 PM
To: Nathan Wilhelmi
Cc: PGSQL Mailing List
Subject: Re: [GENERAL] Script to reset all sequence values in the a
given DB?

On 10/12/2007, Nathan Wilhelmi <[EMAIL PROTECTED]> wrote:
> Hello - Does anyone happen to have a SQL script or function that can
> reset all the sequence values found in a given DB? When we rebuild the
> DB it would be handy to be able to set all the sequence back to a
known
> starting place.
>

create or replace function resetall()
returns void as $$
declare
  v varchar;
  m integer;
begin
  for v in
 select n.nspname || '.' || c.relname
 from pg_catalog.pg_class c
  left join
  pg_catalog.pg_namespace n
  on n.oid = c.relnamespace
where c.relkind = 'S'
  loop
execute 'select min_value from '||v into m;
setval(v, m, false);
  end loop;
  return;
end; $$ language plpgsql;

Regards

Pavel Stehule
> Thanks!
>
> -Nate
>
>
> ---(end of
broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

---(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
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.


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

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

Re: [GENERAL] slony question

[Chris Browne]

[EMAIL PROTECTED] ("Josh Harrison") writes:
> Does slony support postgres major version upgrade ? ie., will it
> replicate between different major versions?

Yes, that's one of the major "use cases" for Slony-I.

Version 1.0 and 1.1 support (loosely) from PostgreSQL 7.3 to 8.1-ish.

Version 1.2 dropped 7.3 support, but supports up to (just about;
there's a patch not in a 1.2 release that is needed for late-breaking
8.3 changes) 8.3.

You may have to be careful about which version of Slony-I you select
if you have a really old PG database.
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://www3.sympatico.ca/cbbrowne/lisp.html
"What you  said you   want to do  is  roughly  equivalent to   nailing
horseshoes to the tires of your Buick."  -- [EMAIL PROTECTED] on
the question "Why can't Linux use Windows Drivers?"

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

Re: [GENERAL] Script to reset all sequence values in the a given DB?

[Pavel Stehule]

On 10/12/2007, Nathan Wilhelmi <[EMAIL PROTECTED]> wrote:
> Hello - Does anyone happen to have a SQL script or function that can
> reset all the sequence values found in a given DB? When we rebuild the
> DB it would be handy to be able to set all the sequence back to a known
> starting place.
>

create or replace function resetall()
returns void as $$
declare
  v varchar;
  m integer;
begin
  for v in
 select n.nspname || '.' || c.relname
 from pg_catalog.pg_class c
  left join
  pg_catalog.pg_namespace n
  on n.oid = c.relnamespace
where c.relkind = 'S'
  loop
execute 'select min_value from '||v into m;
setval(v, m, false);
  end loop;
  return;
end; $$ language plpgsql;

Regards

Pavel Stehule
> Thanks!
>
> -Nate
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

---(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

[GENERAL] comparing rows

[hjenkins]

Hello, all,

I would like to take a timeseries of data and extract the rows of data
flanking the gaps in it. So I need to compare timestamps from two adjacent
rows, and determine if the interval is greater than the standard sampling
interval.

Thanks for any help.

Regards,
H. Jenkins


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

Re: [GENERAL] Unable to ALTER table after SELECT data from table

[Scott Marlowe]

On Dec 10, 2007 2:48 PM, Keary Suska <[EMAIL PROTECTED]> wrote:
> on 12/10/07 12:15 PM, [EMAIL PROTECTED] purportedly said:
>
> > To work around this I add an dbCon.rollBack() after select statement
> > above in good and in bad times. After that ALTER works.
> > Can someone explain me why I need this rollback ?
>
> You only need the rollback when the SELECT statement fails. When an error
> occurs within a transaction, the transaction is aborted but not rolled back.
> There are likely reasons why the rollback isn't automatic, but I don't know
> them.

I think you confuse postgresql with some other database.  Without
setting a savepoint, any error during a transaction and roll it back
upon connection close | rollback | commit.

I.e. the rollback IS automatic.  Just not necessarily immediate.

> Your choice is either to run the commands in an "autocommit" mode or, as you
> are doing, rollback (or commit, if there was no error) after the SELECT.

You can commit with an error.  It'll do the same thing as a rollback;

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

Re: [GENERAL] Unable to ALTER table after SELECT data from table

[Martijn van Oosterhout]

On Mon, Dec 10, 2007 at 08:13:09PM +0100, Thomas Carsten Franke wrote:
> If I do so I get following error by Postgres:
> 
> org.postgresql.util.PSQLException: ERROR: current transaction is
> aborted, commands ignored until end of transaction block

It means exactly what it says. You (or Java for you) started a
transaction block and everything in a transaction block is either
committed or aborted. Once an error has been raised, everything after
that is ignore till the end of the transaction.

> To work around this I add an dbCon.rollBack() after select statement
> above in good and in bad times. After that ALTER works.
> Can someone explain me why I need this rollback ?

Rollback/commit either will do. You just need ot start a new
transaction. What people usually do is to do stuff like what you're
doing outside any transactions, thus avoiding the whole problem.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] Simpler dump?

[Raymond O'Donnell]

On 10/12/2007 16:39, Douglas McNaught wrote:

It sounds like the thing for you to do is drop template1 (which will
have no effect on template_postgis), create it again from template0,
and use template_postgis when you need it (otherwise template1 will be
used by default for new databases).


As I recall, that's exactly what template0 is for - it's to let you 
restore template1 to pristine condition if you fill it with crud (which 
I've managed to do once or twice).


Ray.


---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

---(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] Unable to ALTER table after SELECT data from table

[Keary Suska]

on 12/10/07 12:15 PM, [EMAIL PROTECTED] purportedly said:

> To work around this I add an dbCon.rollBack() after select statement
> above in good and in bad times. After that ALTER works.
> Can someone explain me why I need this rollback ?

You only need the rollback when the SELECT statement fails. When an error
occurs within a transaction, the transaction is aborted but not rolled back.
There are likely reasons why the rollback isn't automatic, but I don't know
them.

Your choice is either to run the commands in an "autocommit" mode or, as you
are doing, rollback (or commit, if there was no error) after the SELECT.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"



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

[GENERAL] Restore problem

[Keith Turner]

Hi first post here, I hope you can help.

We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on
someone else's network, though we do have anti-virus running, the
symptoms were worrying - so we had to wipe and reinstall the OS and all
programs. We had recovered the 8.1 folder and the data off the crashed
computer prior to the wipe, but the last good .backup file is a few days
older than that.

Are there step by step instructions on restoring from the folder itself
instead of a backup file? Is it even possible?

Thanks,

Keith


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

   http://archives.postgresql.org/

Re: [GENERAL] TIMESTAMP difference

[Rodrigo De León]

On Dec 10, 2007 2:13 PM, rihad <[EMAIL PROTECTED]> wrote:
> Hi, is there a way to get the difference in hours between two
> timestamps?

SELECT (EXTRACT (EPOCH FROM TIMESTAMP '20071211 00:00') - EXTRACT
(EPOCH FROM TIMESTAMP '20071209 01:00')) * INTERVAL '1 second';

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Script to reset all sequence values in the a given DB?

[Nathan Wilhelmi]

Hello - Does anyone happen to have a SQL script or function that can 
reset all the sequence values found in a given DB? When we rebuild the 
DB it would be handy to be able to set all the sequence back to a known 
starting place.


Thanks!

-Nate


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

Re: [GENERAL] partitioned table query question

[Vivek Khera]

On Dec 10, 2007, at 1:21 PM, Erik Jones wrote:

You beat me to the punch on this one.  I was wanting to use modulo  
operations for bin style partitioning as well, but this makes things  
pretty awkward as well as unintuitive.  So, to the postgres gurus:
What are the limitations of check constraints when used with  
constraint exclusion?  Is this really the intended behavior?




/me too!

I have vague recollection of reading that the constraints on the child  
tables needed to be free of computation (ie, just straight comparison  
ranges) but I can't find that reference now.


But in my case, I can almost always pick the appropriate sub-table  
from the application level anyway.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Unable to ALTER table after SELECT data from table

[Thomas Carsten Franke]

Hi,
following I tried for some application logic based data migration

Statement stmt = dbCon.createStatement();
try {
  ResultSet geo_columns_rs = stmt.
 executeQuery("SELECT baseline_check_version from geodb limit 1");
source_version = Versions.R03_00;
  source_version = Versions.R03_00;
}catch (Exception e) {}


to check if column already exists...
If I get an exception in Java I remember that and try to update my
database structure to new version using following:

dbCon.createStatement().execute("ALTER TABLE geodb ADD COLUMN
baseline_check_version VARCHAR(32)");

If I do so I get following error by Postgres:

org.postgresql.util.PSQLException: ERROR: current transaction is
aborted, commands ignored until end of transaction block
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
   at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
   at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)
   at
de.airbus.omts.geodbgen.communication.dbms.Checker.execute(Checker.java:244)
   at
de.airbus.omts.geodbgen.communication.dbms.Checker.migrateDB(Checker.java:206)
   at
de.airbus.omts.geodbgen.communication.dbms.Checker.main(Checker.java:390)


To work around this I add an dbCon.rollBack() after select statement
above in good and in bad times. After that ALTER works.
Can someone explain me why I need this rollback ?
Using:
Postgres 8.1.4-1, JDBC postgresql-8.1-405.jdbc3, JDK 1.6
Same problem with JDK 1.5...

Thanks

thomas
-- 
 Mit freundlichen Grüßen
 Brunel GmbH

 Dipl.-Inf. Thomas Carsten Franke
 - Senior Software Designer -

 Brunel GmbH
 Bereich Communications
 Daimlerring 9
 D 31135 Hildesheim, Germany

 Telefon: 05121 1760-820
 Telefax: 05121 1760-999
 E-Mail: [EMAIL PROTECTED]
 Internet: www.brunel.de

 Hauptsitz: Airport City, Hermann-Köhl-Str. 1 a, 28199 Bremen
 Amtsgericht Bremen HRB 16935
 General Manager: Carsten Siebeneich

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

[GENERAL] TIMESTAMP difference

[rihad]

Hi, is there a way to get the difference in hours between two 
timestamps? The HH{1,}:MM:SS format will do.


foo=> select timestamp '20071211 00:00' - timestamp '20071210 00:01';
 ?column?
--
 23:59:00
(1 row)

foo=> select timestamp '20071211 00:00' - timestamp '20071209 01:00';
?column?

 1 day 23:00:00
(1 row)

Any way to make it return "47:00:00" instead? select interval '47:00:00' 
is still a legal interval as far as postgresql goes.


8.3-beta2 (can't get to beta4: freebsd ports are yet frozen :(

Thanks.

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

[GENERAL] Unable to ALTER table after SELECT data from table

[Thomas Carsten Franke]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,
following I tried for some application logic based data migration

Statement stmt = dbCon.createStatement();
try {
  ResultSet geo_columns_rs = stmt.
 executeQuery("SELECT baseline_check_version from geodb limit 1");
source_version = Versions.R03_00;
  source_version = Versions.R03_00;
}catch (Exception e) {}


to check if column already exists...
If I get an exception in Java I remember that and try to update my
database structure to new version using following:

dbCon.createStatement().execute("ALTER TABLE geodb ADD COLUMN
baseline_check_version VARCHAR(32)");

If I do so I get following error by Postgres:

org.postgresql.util.PSQLException: ERROR: current transaction is
aborted, commands ignored until end of transaction block
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
   at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
   at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)
   at
de.airbus.omts.geodbgen.communication.dbms.Checker.execute(Checker.java:244)
   at
de.airbus.omts.geodbgen.communication.dbms.Checker.migrateDB(Checker.java:206)
   at
de.airbus.omts.geodbgen.communication.dbms.Checker.main(Checker.java:390)


To work around this I add an dbCon.rollBack() after select statement
above in good and in bad times. After that ALTER works.
Can someone explain me why I need this rollback ?
Using:
Postgres 8.1.4-1, JDBC postgresql-8.1-405.jdbc3, JDK 1.6
Same problem with JDK 1.5...

Thanks

thomas
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQEVAwUBR12PxZp7rRUHwFuZAQKnfggAvc8pYllA4RwLJqiiIuxAzF0zq4YcSAAv
Xl9dvKATWEkk1I22YfylAQP4JDzAUx630+0qe3Z6DTOC5hgQMC2yEFftebO/I8cK
9HcGyIJtAq75uwKuKMvoZVGYh0c91xZlRGhtGLUADITqn6L+r9JvnaoaKvZfOZGl
IcubUzw5JoWVANuD+PiePvSWiudYG966UD9eT6xPvYWaybITiof/xd/nlYV0zkdL
ZIHxZKEfS8KngGlqesqJDYM/XedVYvLXGe8vH6QTeNKWAr8l+rrBBseBNUrco6Jz
1w15bxe1k2n6KZbkCn6KhJARo3aw4axKgq0I9HDytFEfmt703U+rZQ==
=3VjN
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/

Re: [GENERAL] SQL design pattern for a delta trigger?

[Vivek Khera]

On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:

You can do this with a conditional.  Something like the following  
should work.


IF
 NOT (a query matching your data returns rows)
THEN
 INSERT (your new data)


There exists a race condition here unless you've locked your tables.

---(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] partitioned table query question

[Erik Jones]

On Dec 7, 2007, at 10:51 PM, Mason Hale wrote:

I'm implementing table partitioning on 8.2.5 -- I've got the tables  
set up to partition based on the % 10 value of a key.


My problem is that I can't get the planner to take advantage of the  
partitioning without also adding a key % 10 to the where clause.

Is there any way around that?

My child table definitions are:

CREATE TABLE topic_version_page_0 (
   CHECK (topic_version_id % 10 = 0::integer )
) inherits (topic_version_page);

...

CREATE TABLE topic_version_page_9 (
   CHECK (topic_version_id % 10 = 9::integer )
) inherits (topic_version_page);


I've also created indexes and constraints for each child table, and  
an insert trigger on the master table (topic_version_page).


If I include a 'topic_version_id % 10 = [some value]' in my query,  
then the partitioning shows up in the query plan:


test=> explain select * from topic_version_page where  
topic_version_id % 10 = (102 % 10) and topic_version_id = 102;
  QUERY  
PLAN
-- 


 Result  (cost=4.27..19.23 rows=2 width=194)
   ->  Append  (cost= 4.27..19.23 rows=2 width=194)
 ->  Bitmap Heap Scan on topic_version_page   
(cost=4.27..9.62 rows=1 width=194)

   Recheck Cond: (topic_version_id = 102)
   Filter: ((topic_version_id % 10) = 2)
   ->  Bitmap Index Scan on  
index_topic_version_page_on_topic_version_id_and_created_at   
(cost=0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_2  
topic_version_page  (cost=4.27..9.62 rows=1 width=194)

   Recheck Cond: (topic_version_id = 102)
   Filter: ((topic_version_id % 10) = 2)
   ->  Bitmap Index Scan on  
index_topic_version_page_2_on_topic_version_id_and_page_id   
(cost=0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
(12 rows)

But if I don't explicitly include a  'topic_version_id % 10' -- the  
plan gets much worse, checking every table (see below).



test=> explain select * from topic_version_page where  
topic_version_id = 102;
  QUERY  
PLAN
-- 


 Result  (cost=4.27..105.68 rows=22 width=194)
   ->  Append  (cost= 4.27..105.68 rows=22 width=194)
 ->  Bitmap Heap Scan on topic_version_page   
(cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan on  
index_topic_version_page_on_topic_version_id_and_created_at  (cost=  
0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_0  
topic_version_page  (cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan on  
index_topic_version_page_0_on_topic_version_id_and_page_id   
(cost=0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_1  
topic_version_page  (cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan on  
index_topic_version_page_1_on_topic_version_id_and_page_id  (cost=  
0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_2  
topic_version_page  (cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan on  
index_topic_version_page_2_on_topic_version_id_and_page_id   
(cost=0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_3  
topic_version_page  (cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan on  
index_topic_version_page_3_on_topic_version_id_and_page_id  (cost=  
0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_4  
topic_version_page  (cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan on  
index_topic_version_page_4_on_topic_version_id_and_page_id   
(cost=0.00..4.27 rows=2 width=0)

 Index Cond: (topic_version_id = 102)
 ->  Bitmap Heap Scan on topic_version_page_5  
topic_version_page  (cost=4.27..9.61 rows=2 width=194)

   Recheck Cond: (topic_version_id = 102)
   ->  Bitmap Index Scan

Re: [GENERAL] SQL design pattern for a delta trigger?

[Ted Byers]

Thanks all.  I tried the appended code in a trigger
function, but postgresql won't take it.

It complains that assets.quantity is not a scalar. 
However, the WHERE clause in that select statement
guarantees that at most only one record will be
returned.  An open position on a given kind of asset
is represented by null in the end_valid_time field,
and the combination of asset_type_id, portfolio_id and
end_valid_time is certain to be unique, if there is a
record for that asset type in that porfolio at all.

I thought I'd try checking for an open position first
because the manual indicated that exception handling
is quite expensive.  But I must have missed something,
because it doesn't like how I tried to define my
trigger function.

I have four sequences, one each for four tables.  Two
of the tables are just look up tables, for asset types
and portfolios; trivial for test case with only an
autoincrementing integer primary key and a "name". 
The other two are the ones of interest.  Assets is
treated as read only as far as the user is concerned. 
The user's data in the assets table is mediated
through transactions inserted (and NEVER deleted or
updated), into the transactions table.  Assets has the
minimal suite of columns (autoincrementing integer
primary key, asset_typeID, portfolio_id, all integers,
quantity with is a floating point number and two
dates: start_valid_time and end_valid_time). 
Transactions has only a transaction_id, portfolio_id,
asset_type_id, quantity and transaction_date.  There
are of course foreign keys connectin the assets and
transactions tables to the lookup tables, and a
composite index on assets to make looking up records
based on portfolio_id, asset_id and end_valid_time as
quick as possible.  It couldn't be simpler,
conceptually!  yet I must have missed something, cause
postgresql won't accept the function body I show
below.

If I can't get this working quickly, I may just resort
to creating a stored procedure that takes the
transaction details as arguments and processes both
tables appropriately without relying on a trigger. 
:-(

Thanks for everyone's help.

Ted



===
DECLARE
  id BIGINT;
  q DOUBLE PRECISION;
BEGIN
  SELECT assets.id INTO id, assets.quantity INTO q
FROM assets
WHERE assets.asset_type_id = NEW.asset_type_id
  AND assets.portfolio_id = NEW.portfolio_id
  AND assets.end_valid_time IS NULL;
  IF (id IS NULL) THEN
INSERT INTO assets (asset_type_id,
portfolio_id,quantity,start_valid_stime,end_valid_time)
  VALUES (NEW.asset_type_id,NEW.portfolio_id,
NEW.quantity, NEW.transaction_date,NULL);
  ELSE
UPDATE assets SET end_valid_time =
NEW.transaction_date WHERE id = id;
INSERT INTO assets (asset_type_id,
portfolio_id,quantity,start_valid_stime,end_valid_time)
  VALUES (NEW.asset_type_id,NEW.portfolio_id, q +
NEW.quantity, NEW.transaction_date,NULL);
  END
END


---(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] Creating indexes

[Scott Marlowe]

On Dec 10, 2007 10:53 AM, Robert Fitzpatrick <[EMAIL PROTECTED]> wrote:
> I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
> to almost 10,000 loading 25 per page. There is a filter feature atop all
> seven columns in the table listing (all varchar except one date column).
> Also, sorting can be done by clicking any column header. Some complain
> of speed during filtering or clearing the filter. I want to create some
> indexes to see if this will help as I'm sure it will since there are
> none currently.
>
> Now my question, would it be better to create one index with all columns
> in the table -or- a separate index for each column field? I was assuming
> the latter, but would the index with all columns be beneficial as well?

As previously mentioned, making multicolumn indexes may not be your best bet.

If you have an index on (field1, field2, field3) and do a query that
doesn't select based on field1, you won't use that index.  OTOH, if
you always select certain fields for order by / where fieldx= then it
might be a good bet.

Most importantly, if your database is not initialized to locale=C,
then you will need to use varchar_ops operators on it.  Otherwise your
db won't be able to use your indexes.

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

Re: [GENERAL] slony question

[Josh Harrison]

Thanks...Ill check that list
josh

On Dec 10, 2007 12:37 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Mon, 10 Dec 2007 12:20:07 -0500
> "Josh Harrison" <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> > Does slony support postgres major version upgrade ? ie., will it
> > replicate between different major versions?
>
> Yes. But for further questions on Slony please see the slony lists:
>
> http://lists.slony.info/mailman/listinfo
>
> Sincerely,
>
> Joshua D. Drake
>
> > Thanks
> > josh
>
>
> - --
>
>  === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
> PostgreSQL solutions since 1997  http://www.commandprompt.com/
>UNIQUE NOT NULL
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFHXXlfATb/zqfZUUQRAn5jAJ9iounFrKiLUSv/Eo5c4KaBZl5QRQCgogOV
> 5On/T8c/7xMFQ6UrvPpMhpE=
> =4gk8
> -END PGP SIGNATURE-
>

Re: [GENERAL] Simpler dump?

[Ted Byers]

Thanks All.  I learned plenty this morning.
--- Douglas McNaught <[EMAIL PROTECTED]> wrote:

> On 12/10/07, Ted Byers <[EMAIL PROTECTED]>
> wrote:
> 
> > OK.  A worry.  How is template_postgis
> constructed?
> > Is it just a handy reference to template1?  Or
> does it
> > exist independantly?  I don't want to be dropping
> > template1 only to find that breaking
> template_postgis.
> 
> All databases are separate entities--the data copy
> only happens at
> creation time, based on the template you specify. 
> The
> 'template_postgis' DB is not a standard PG database,
> so either it's
> created by the PostGIS install, or someone at your
> site set it up.
> 
> It sounds like the thing for you to do is drop
> template1 (which will
> have no effect on template_postgis), create it again
> from template0,
> and use template_postgis when you need it (otherwise
> template1 will be
> used by default for new databases).
> 
Great!  Thanks for this.  I appreciate it.

Ted

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

Re: [GENERAL] slony question

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Mon, 10 Dec 2007 12:20:07 -0500
"Josh Harrison" <[EMAIL PROTECTED]> wrote:

> Hi,
> Does slony support postgres major version upgrade ? ie., will it
> replicate between different major versions?

Yes. But for further questions on Slony please see the slony lists:

http://lists.slony.info/mailman/listinfo

Sincerely,

Joshua D. Drake

> Thanks
> josh


- -- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHXXlfATb/zqfZUUQRAn5jAJ9iounFrKiLUSv/Eo5c4KaBZl5QRQCgogOV
5On/T8c/7xMFQ6UrvPpMhpE=
=4gk8
-END PGP SIGNATURE-

---(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] Creating indexes

[André Volpato]

Robert Fitzpatrick escreveu:

I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
to almost 10,000 loading 25 per page. There is a filter feature atop all
seven columns in the table listing (all varchar except one date column).
Also, sorting can be done by clicking any column header. Some complain


You can try transfer the sort loading to the client, using tablesort:
http://tablesorter.com/docs/

I dont know if the performance will be ok with 10k rows but...

Also, you can create the various tables in background, one html for each 
sorted column.

When the user clicks, you show the desired html.

--

[]´s,

André Volpato
Ecom Tecnologia LTDA - Análise e Desenvolvimento
[EMAIL PROTECTED]



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

  http://archives.postgresql.org/

Re: [GENERAL] slony question

[Raymond O'Donnell]

On 10/12/2007 17:20, Josh Harrison wrote:

Does slony support postgres major version upgrade ? ie., will it 
replicate between different major versions?


You'll get a more definitive answer on the Slony list, but I think so - 
IIRC that's one of Slony's /raisons d'etre/.


Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

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

  http://archives.postgresql.org/

[GENERAL] slony question

[Josh Harrison]

Hi,
Does slony support postgres major version upgrade ? ie., will it replicate
between different major versions?
Thanks
josh

Re: [GENERAL] Creating indexes

[Matthew T. O'Connor]

Robert Fitzpatrick wrote:

Now my question, would it be better to create one index with all columns
in the table -or- a separate index for each column field? I was assuming
the latter, but would the index with all columns be beneficial as well?



Generally it's much better to have an index deal with only one column. 
Also I wouldn't just randomly throw an index on every column.  You 
should only index the columns that used for the constraints.  You might 
benefit from turning up the logging and looking at what queries are 
slow, then play with them and viewing their explain output with / 
without indexes.


Extra indexes that aren't really doing anything can actually hurt 
performance during vacuum or insert / update / delete.


Matt


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

Re: [GENERAL] Simpler dump?

[Obe, Regina]

 
I think PgAdmin ,in 1.8 at least, is by default set to hide system
objects like the template databases.

To enable this 
go to File->Options->Display and make sure to check the "Show system
objects in treeview".  You may want to check some of the other options
as well.

Hope that helps,
Regina
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Douglas
McNaught
Sent: Monday, December 10, 2007 11:27 AM
To: Ted Byers
Cc: Richard Huxton; Tino Wildenhain; Uwe C. Schroeder;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simpler dump?

On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:

> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal template1 rather than
> template_postgis, but leaving the latter in place so I
> can use it when I need it?

Try using the 'psql' command line tool to list your databases--it
sounds like pgAdmin might be hiding some of them from you (which isn't
the fault of anyone on this list since pgAdmin is a separate project).

It's certainly conceivable that someone before you set up
template_postgis as the default template database, but getting an
accurate catalog of what you've got sounds like the first step.

-Doug

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

   http://archives.postgresql.org/
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.


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

[GENERAL] Creating indexes

[Robert Fitzpatrick]

I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
to almost 10,000 loading 25 per page. There is a filter feature atop all
seven columns in the table listing (all varchar except one date column).
Also, sorting can be done by clicking any column header. Some complain
of speed during filtering or clearing the filter. I want to create some
indexes to see if this will help as I'm sure it will since there are
none currently.

Now my question, would it be better to create one index with all columns
in the table -or- a separate index for each column field? I was assuming
the latter, but would the index with all columns be beneficial as well?

Thanks in advance!

-- 
Robert


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

Re: [GENERAL] Simpler dump?

[Douglas McNaught]

On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:

> OK.  A worry.  How is template_postgis constructed?
> Is it just a handy reference to template1?  Or does it
> exist independantly?  I don't want to be dropping
> template1 only to find that breaking template_postgis.

All databases are separate entities--the data copy only happens at
creation time, based on the template you specify.  The
'template_postgis' DB is not a standard PG database, so either it's
created by the PostGIS install, or someone at your site set it up.

It sounds like the thing for you to do is drop template1 (which will
have no effect on template_postgis), create it again from template0,
and use template_postgis when you need it (otherwise template1 will be
used by default for new databases).

-Doug

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

Re: [GENERAL] Simpler dump?

[Douglas McNaught]

On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:

> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal template1 rather than
> template_postgis, but leaving the latter in place so I
> can use it when I need it?

Try using the 'psql' command line tool to list your databases--it
sounds like pgAdmin might be hiding some of them from you (which isn't
the fault of anyone on this list since pgAdmin is a separate project).

It's certainly conceivable that someone before you set up
template_postgis as the default template database, but getting an
accurate catalog of what you've got sounds like the first step.

-Doug

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

   http://archives.postgresql.org/

Re: [GENERAL] Simpler dump?

[Ted Byers]

Thanks Richard.

--- Richard Huxton <[EMAIL PROTECTED]> wrote:

> Ted Byers wrote:
> > Amyway, when I look at the server using pgadmin, I
> > don't see either template0 or template1.  I see
> only
> > template_postgis.  Should I be able to see
> template0
> > and template1 among the databases on the server,
> or
> > are they normally hidden to minimise the chances
> of
> > getting them screwed up.
> 
> There'll be an option in pgadmin somewhere to show
> them. Not sure where 
> I'm afraid, I mostly use the command-line.
> 
Found it.  Under the "view" menu, the last item is
"System object".  That now shows the template0 and
template1.  It won't let me examine the contents of
template0, BUT ...  It looks like template1 is a copy
of template_postgis.  I see the same stuff in the two.

> > At this stage, how can I get a template I can use
> by
> > default that doesn't include the postgis stuff, so
> I
> > can use the template_postgis only when I need it?
> 
> You can just drop template1 and re-create it using
> template0 (which is 
> read-only) as it's template. Check the docs for
> CREATE DATABASE and 
> google a little for examples.
> 
OK.  A worry.  How is template_postgis constructed? 
Is it just a handy reference to template1?  Or does it
exist independantly?  I don't want to be dropping
template1 only to find that breaking template_postgis.

A metaphore might explain the origin of my worry.  I
use both C++ and Java.  In C++, copy/assignment gives
you two different objects with the same data and
structure.  Assignment in Java gives you two
references to the same object, so changes using the
one reference will be seen when examining the other
(this is a common gotcha for beginning developers
using both C++ and Java - the way to get C++
functionality inJava is to use operator new and the
class' copy constructor).  So, in adding postgis
support, is the template_postgis creation more
analogous to assignment in C++ or to assignment in
Java?

Thanks

Ted

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

Re: [GENERAL] Simpler dump?

[Richard Huxton]

Ted Byers wrote:

OK, Now I am a bit confused.  The manual doesn't say
what to do if you don't see template1 or template0 on
the server, or even whether or not you should be able
to see them when using a tool like pgAdmin.  But it
does say:


Well, it won't mention pgadmin because that's a separate project.


"The postgres database is also created when a database
cluster is initialized. This database
is meant as a default database for users and
applications to connect to. It is simply a copy of
template1 and may be dropped and recreated if
required."

Now, when I look at postgres, it is empty, apart from
one public schema, and all the items (aggregates &c.)
have nothing in them.


So presumably it was created before you added these things to template1

> The manual talks about creating

a template from an existing database, but not about
how to create a template from a template, apart from
:"template1 is the default source database name for
CREATE DATABASE. For example, one could
drop template1 and recreate it from template0 without
any ill effects."

Obviously, template1 must have been either blown away
by the creation of template_postgis, or the one has
been made to be an alias for the other, or the default
template has been set to use template_postgis.  And if
postgres is a copy of template0, and template1 starts
as a copy of template0, and all my databases have
included the postgis stuff, then template_postgis is
the template being used by default.


I think you're jumping to conclusions.

You can't see template0/1 becuase there is a tick-box somewhere in 
pgadmin saying "hide system things" or some such.


The postgres database was created from template1 (because it's the first 
"usable" db that gets created) and then you added various bits to template1.



Here's a simple thing to try - create a new database from pgadmin, and 
it will presumably allow you to choose the template DB. I'm guessing 
it'll have template_postgis in the drop-down but if it contains 
template1/0 too then you know they're still there.



So, how do I determine whether or not template1 really
exists on my server and is a copy of template0 (as I'd
infer from what I see in postgres) rather than
template_postgis, and then modify things so that the
default is the normal template1 rather than
template_postgis, but leaving the latter in place so I
can use it when I need it?


You can either use psql (the command-line tool) or find that tick-box in 
pgadmin. It's unlikely anything has deleted template1 (unless you have 
manually). I'm not sure it's possible to drop template0 without really 
working at it.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Planner not using UNIQUEness of index properly

[John Burger]

Tom Lane wrote:


I would think UNIQUE => one row is pretty obvious - what am I
missing?  (Unless it's that I'm still stuck in 7.4.)


That would be the problem :-( ... a look at the code suggests that the
ability to do anything intelligent with expression indexes was added
in 8.0.


Whaa.  Okay, more ammunition, thanks.

- John D. Burger
  MITRE



---(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] Simpler dump?

[Tom Lane]

Ted Byers <[EMAIL PROTECTED]> writes:
> OK, Now I am a bit confused.  The manual doesn't say
> what to do if you don't see template1 or template0 on
> the server, or even whether or not you should be able
> to see them when using a tool like pgAdmin.

Our manual is not in charge of documenting pgAdmin.

Perhaps you should go to the pgadmin lists and discuss
with them whether auto-hiding these databases is such
a good idea.

Or at least dare to touch "psql -l" so that you can find out
whether pgAdmin is hiding them or not.

regards, tom lane

---(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] Planner not using UNIQUEness of index properly

[Scott Marlowe]

On Dec 10, 2007 9:32 AM, John Burger <[EMAIL PROTECTED]> wrote:
> I have a unique function index on one of my tables:
>
> create table allWords (
>wordID   serial  PRIMARY KEY,
>word textNOT NULL
> );
> create unique index ix_allWords_lower on allWords (lower(word));
>
> To my surprise, the planner does not seem to realize that only one
> row can result from using this index:

that's certainly not what I'm seeing in pgsql 8.2.5 here.

I'm guessing it has to do with being stuck on 7.4.  I found a LOT of
cases where 8.2.5 handles index conditions smarter than 7.4 did.  For
instance this:

select * from table where timestampfield between now() - interval '1
day' and now()

will always generate a seq scan in 7.4 regardless of indexes.  In 8.1
and 8.2 pgsql knows how to use an index.

---(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] Planner not using UNIQUEness of index properly

[Tom Lane]

John Burger <[EMAIL PROTECTED]> writes:
> create unique index ix_allWords_lower on allWords (lower(word));

> To my surprise, the planner does not seem to realize that only one  
> row can result from using this index:

> I would think UNIQUE => one row is pretty obvious - what am I  
> missing?  (Unless it's that I'm still stuck in 7.4.)

That would be the problem :-( ... a look at the code suggests that the
ability to do anything intelligent with expression indexes was added
in 8.0.

regards, tom lane

---(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] Simpler dump?

[Ted Byers]

OK, Now I am a bit confused.  The manual doesn't say
what to do if you don't see template1 or template0 on
the server, or even whether or not you should be able
to see them when using a tool like pgAdmin.  But it
does say:

"The postgres database is also created when a database
cluster is initialized. This database
is meant as a default database for users and
applications to connect to. It is simply a copy of
template1 and may be dropped and recreated if
required."

Now, when I look at postgres, it is empty, apart from
one public schema, and all the items (aggregates &c.)
have nothing in them.  The manual talks about creating
a template from an existing database, but not about
how to create a template from a template, apart from
:"template1 is the default source database name for
CREATE DATABASE. For example, one could
drop template1 and recreate it from template0 without
any ill effects."

Obviously, template1 must have been either blown away
by the creation of template_postgis, or the one has
been made to be an alias for the other, or the default
template has been set to use template_postgis.  And if
postgres is a copy of template0, and template1 starts
as a copy of template0, and all my databases have
included the postgis stuff, then template_postgis is
the template being used by default.

So, how do I determine whether or not template1 really
exists on my server and is a copy of template0 (as I'd
infer from what I see in postgres) rather than
template_postgis, and then modify things so that the
default is the normal template1 rather than
template_postgis, but leaving the latter in place so I
can use it when I need it?

Thanks

Ted

---(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] Simpler dump?

[Richard Huxton]

Ted Byers wrote:

Amyway, when I look at the server using pgadmin, I
don't see either template0 or template1.  I see only
template_postgis.  Should I be able to see template0
and template1 among the databases on the server, or
are they normally hidden to minimise the chances of
getting them screwed up.


There'll be an option in pgadmin somewhere to show them. Not sure where 
I'm afraid, I mostly use the command-line.



At this stage, how can I get a template I can use by
default that doesn't include the postgis stuff, so I
can use the template_postgis only when I need it?


You can just drop template1 and re-create it using template0 (which is 
read-only) as it's template. Check the docs for CREATE DATABASE and 
google a little for examples.


You can probably do all this from pgadmin too, of course.

--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Simpler dump?

[Ted Byers]

--- Richard Huxton <[EMAIL PROTECTED]> wrote:

> Tino Wildenhain wrote:
> > Hi Ted,
> > 
> > Ted Byers wrote:
> >> Thanks Uwe
> >>
> >> This is a great start.  It reduces the dump from
> 2 MB
> >> down to 167K, but out of 6833 lines of SQL, 5744
> >> relate to the public schema in the DB, and I
> didn't
> >> touch that.  It has over a dozen types, 419
> functions,
> >> &c., that were put there by postgresql the moment
> I
> >> created the database.
> 
> > Well thats usually not the case unless you changed
> > the default database per accident. 
> 
> The database is called "template1" See the manuals
> regarding "CREATE 
> DATABASE" for details.
> 
> 
Thanks Richard.  To date, I never worried about
templates for my databases.  I just created them, and
when I needed to deploy them, I dumped them, put the
dump file on a memoery stick and carried it physically
to the production server and restored there.

Anyway, the extra stuff i don't need for this specific
database appears to be for postgis, which I'd enabled
when I installed  because I need gis capability for
another database.  But it seems to be putting gis
support in all of the databases I created.  I'd
thought that, by enabling it, I'd be able to turn it
on when I needed it.

Amyway, when I look at the server using pgadmin, I
don't see either template0 or template1.  I see only
template_postgis.  Should I be able to see template0
and template1 among the databases on the server, or
are they normally hidden to minimise the chances of
getting them screwed up.

At this stage, how can I get a template I can use by
default that doesn't include the postgis stuff, so I
can use the template_postgis only when I need it?

Thanks

Ted

---(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] Simpler dump?

[Tino Wildenhain]

Douglas McNaught wrote:

On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:

Where will I find template1?  When I look at the
databases on the server, the only template I see is
called "template_postgis".  Most of the extra stuff I
see in all my databases relates to geometry that I
find in this template.  When I installed postgresql, I
enabled postgis because I need it for some, but not
all, of my databases.  Is it possible to have more
than one template, and to specify which template to
use when creating a new DB?


Yes, looking at the docs for CREATE DATABASE would probably be
enlightnening for you.


waiting for the topic called: "simpler CREATE DATABASE" coming
up on this list ;-) SCNR ;)

T.

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

  http://archives.postgresql.org/

Re: [GENERAL] Simpler dump?

[Tino Wildenhain]

Hi Ted,

Ted Byers wrote:

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

...

it's not pg_dump's fault --- you need to clean out
template1.


Thanks Tom,

Where will I find template1?  When I look at the
databases on the server, the only template I see is
called "template_postgis".  Most of the extra stuff I
see in all my databases relates to geometry that I
find in this template.  When I installed postgresql, I
enabled postgis because I need it for some, but not
all, of my databases.  Is it possible to have more
than one template, and to specify which template to
use when creating a new DB?


Yes thats possible - createdb has an option -T to
specify another template database.

Regards
Tino

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

Re: [GENERAL] Slony replication

[Vivek Khera]

On Dec 8, 2007, at 9:21 AM, Geoffrey wrote:

I am quite new to Slony as well, but one of the first requirements  
the docs state is:


Thus, examples of cases where Slony-I probably won't work out well  
would include:


   * Sites where connectivity is really "flakey"
   * Replication to nodes that are unpredictably connected.

So I suspect Slony is not a solution for your effort.  See:


If your DB doesn't change very much (like a few hundred or thousand  
update/insert/delete per day), then slony can work just fine in such a  
batch mode.  Things break down when you accumulate several hundred  
thousand or more changes between times when you're connected.



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

Re: [GENERAL] Simpler dump?

[Douglas McNaught]

On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
>
> Where will I find template1?  When I look at the
> databases on the server, the only template I see is
> called "template_postgis".  Most of the extra stuff I
> see in all my databases relates to geometry that I
> find in this template.  When I installed postgresql, I
> enabled postgis because I need it for some, but not
> all, of my databases.  Is it possible to have more
> than one template, and to specify which template to
> use when creating a new DB?

Yes, looking at the docs for CREATE DATABASE would probably be
enlightnening for you.

-Doug

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

Re: [GENERAL] Simpler dump?

[Ted Byers]

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

> Ted Byers <[EMAIL PROTECTED]> writes:
> > It seems the public schema is
> > automagically created by Postgres every time I
> create
> > a new database on a given server, and it has over
> a
> > dozen types, over 400 functions, &c.  I don't
> really
> > understand why it needs to be duplicated in every
> Db
> > on a server, but that is another matter.
> 
> What it sounds like to me is that you've managed to
> clutter your
> template1 with a lot of stuff you don't actually
> want, and that's
> getting propagated into new databases by CREATE
> DATABASE.  If so,
> it's not pg_dump's fault --- you need to clean out
> template1.
> 
Thanks Tom,

Where will I find template1?  When I look at the
databases on the server, the only template I see is
called "template_postgis".  Most of the extra stuff I
see in all my databases relates to geometry that I
find in this template.  When I installed postgresql, I
enabled postgis because I need it for some, but not
all, of my databases.  Is it possible to have more
than one template, and to specify which template to
use when creating a new DB?

Thanks again,

Ted

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

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

[GENERAL] Planner not using UNIQUEness of index properly

[John Burger]

I have a unique function index on one of my tables:

create table allWords (
  wordIDserial  PRIMARY KEY,
  word  textNOT NULL
);
create unique index ix_allWords_lower on allWords (lower(word));

To my surprise, the planner does not seem to realize that only one  
row can result from using this index:


=> explain analyze select * from allwords where lower(word) = 'dog';
QUERY PLAN
 
--
 Index Scan using ix_allwords_lower on allwords  (cost=0.00..2278.27  
rows=763 width=19) (actual time=5.385..5.390 rows=1 loops=1)

   Index Cond: (lower(word) = 'dog'::text)
 Total runtime: 5.482 ms
(3 rows)

Oddly, it does project one row for the caseful lookup.

=> explain analyze select * from allwords where word = 'dog';
   QUERY PLAN
 
-
 Seq Scan on allwords  (cost=0.00..3007.16 rows=1 width=19) (actual  
time=76.197..303.770 rows=1 loops=1)

   Filter: (word = 'dog'::text)
 Total runtime: 303.858 ms
(3 rows)

It's using the index in the first query, but the bad projection seems  
to screw up more complicated joins where the planner decides to use a  
table scan, resulting in plans a thousand times slower (yes, I did  
explain analyze).  This is with a stats target of 100.


I would think UNIQUE => one row is pretty obvious - what am I  
missing?  (Unless it's that I'm still stuck in 7.4.)  Sorry if this  
is well-known - I couldn't find anything in the archives.


Thanks.

- John D. Burger
  MITRE



---(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] Re: [GENERAL] cannot dump structures

[Tom Lane]

=?us-ascii?Q?Martin=20Korous?= <[EMAIL PROTECTED]> writes:
> <  You're running a 7.something pg_dump against an 8.something server.
> <  This will not work because that pg_dump doesn't know about 8.x
> <  catalog layout.

> its standard answer for message `ERROR:  column "datpath"`
> but I wrote:

> DATABASE:
> #/var/pgsql/bin/pg_dump --version
> pg_dump (PostgreSQL) 8.2.5

A look at the source code is sufficient to prove that the string
"datpath" occurs nowhere in 8.2 pg_dump (nor indeed in any 8.x version).
Therefore, you are running pg_dump 7.x, whether you believe it or not.
The error message you'd get if you removed the -i switch would provide
further proof, if you require it.

> pg_dump out of chroot work
> in chroot doesnt work...mystery

Doesn't sound very mysterious to me.  You've got an old copy of pg_dump
somewhere inside the chroot, and perhaps a PATH setting different from
what you think it is.

regards, tom lane

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

[GENERAL] Re: [GENERAL] cannot dump structures

[Martin Korous]

<  You're running a 7.something pg_dump against an 8.something server.
<  This will not work because that pg_dump doesn't know about 8.x
<  catalog layout.

its standard answer for message `ERROR:  column "datpath"`
but I wrote:

DATABASE:
#/var/pgsql/bin/pg_dump --version
pg_dump (PostgreSQL) 8.2.5

and

WEBSERVER in CHROOT:
#chroot /usr/local/chroot/apache_phpPgAdmin /var/pgsql/bin/pg_dump --version
pg_dump (PostgreSQL) 8.2.5

<  My advice is never, never, never use the -i option to pg_dump.
<  It is generally only useful for shooting yourself in the foot,
<  as is happening here.

dump only data is not good too, works only with -i

<  You need to reconsider "impossible".  Maybe your search path
<  isn't what you think it is.

I dont use search path, i wrote absolute path:
$cmd = '/var/pgsql/bin/pg_dump dbname -U username -h hostname -s -i';

pg_dump out of chroot work
in chroot doesnt work...mystery

regards 
Martin Korous


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

Re: [GENERAL] Simpler dump?

[Tom Lane]

Ted Byers <[EMAIL PROTECTED]> writes:
> It seems the public schema is
> automagically created by Postgres every time I create
> a new database on a given server, and it has over a
> dozen types, over 400 functions, &c.  I don't really
> understand why it needs to be duplicated in every Db
> on a server, but that is another matter.

What it sounds like to me is that you've managed to clutter your
template1 with a lot of stuff you don't actually want, and that's
getting propagated into new databases by CREATE DATABASE.  If so,
it's not pg_dump's fault --- you need to clean out template1.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Problems with acessing xml functions on other database

[Nikolay Samokhvalov]

On Dec 9, 2007 10:34 PM, x asasaxax <[EMAIL PROTECTED]> wrote:
> Hi everyone,
>
>I had the folowing problem: when i try to execute a xml_string function
> on a database 'a' it works, but when i try this in database 'b' it doesen´t
> works. Did anyone knows what its going on? Is that some kind of permission?

Apparently, there is contrib/xml2 installed in database 'a'. Use
contrib/xml2's installation SQL script to install this module in
database 'b'.

-- 
Nikolay Samokhvalov  <[EMAIL PROTECTED]>
http://nikolay.samokhvalov.com

Postgresmen http://postgresmen.ru
OpenWebTechnologies http://openwebtech.ru

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

   http://archives.postgresql.org/

Re: [GENERAL] Simpler dump?

[Richard Huxton]

Tino Wildenhain wrote:

Hi Ted,

Ted Byers wrote:

Thanks Uwe

This is a great start.  It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that.  It has over a dozen types, 419 functions,
&c., that were put there by postgresql the moment I
created the database.



Well thats usually not the case unless you changed
the default database per accident. 


The database is called "template1" See the manuals regarding "CREATE 
DATABASE" for details.



--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Simpler dump?

[Tino Wildenhain]

Hi Ted,

Ted Byers wrote:

Thanks Uwe

This is a great start.  It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that.  It has over a dozen types, 419 functions,
&c., that were put there by postgresql the moment I
created the database.  I'd expect the same stuff to be
there the moment I issue the create database directive
on the host machine, so all I really want is the dozen
sequences, two dozen tables, and the suite of
constraints I created, all in the schema specific to
my new DB.


Well thats usually not the case unless you changed
the default database per accident. You can hope but
not be sure to find the same situation on your
server.


Is there a reason pg_dump dumps the stuff in public
even though that stuff seems to be created, and
therefore present, in every database I create on a
given server instance?  Isn't that duplication a waste
of space, and it's presence in the dump a waste of CPU
cycles?


Well, at the moment you seem to waste CPU cycles, network
bandwith and storage on the mailinglist server by not
just looking at the manual of pg_dump, which has for example
goodies as:

-n schema
--schema=schema
  Dump  only schemas matching schema; this selects both the
  ...


HTH ;)

Tino

---(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] Simpler dump?

[Ted Byers]

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

> Ted Byers <[EMAIL PROTECTED]> writes:
> > Is there a way to tell pg_dump to just dump the
> SQL
> > statements required to create the tables,
> sequences,
> > indeces, keys, &c.?
> 
> pg_dump -s ?
> 

Thanks Tom

> > I DON'T need to restore or
> > recreate things like users, or most other kinds of
> DB
> > objects.  Just routine DDL statements.
> 
> pg_dump doesn't try to recreate users, and to most
> people "DDL" would
> include the creation commands for any type of DB
> object whatsoever.
> Your demarcation between stuff you want and stuff
> you don't seems far
> too vaguely stated.
> 
Sorry,

I just wanted the statements I need to recreate the
tables, sequences and constraints I created.  When I
create a database, I leave the default public schema
alone.  The tables, etc. I add are placed in a
separate schema.  It seems the public schema is
automagically created by Postgres every time I create
a new database on a given server, and it has over a
dozen types, over 400 functions, &c.  I don't really
understand why it needs to be duplicated in every Db
on a server, but that is another matter.  In my
database, so far I have only created a dozen sequences
and two dozen tables, along with a suite of
constraints.  So when I look at the dump file, out of
6833 lines of SQL, the first 5744 relate to the
default public schema and only the last 1100 relate to
DB objects I'd created.  I'd assume that the public
schema would be created with the usual stuff when I
create the database name on the production host
anyway, so why recreate all that in the dump file?

Thanks again,

Ted

---(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] Simpler dump?

[Ted Byers]

Thanks Uwe

This is a great start.  It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that.  It has over a dozen types, 419 functions,
&c., that were put there by postgresql the moment I
created the database.  I'd expect the same stuff to be
there the moment I issue the create database directive
on the host machine, so all I really want is the dozen
sequences, two dozen tables, and the suite of
constraints I created, all in the schema specific to
my new DB.

Is there a reason pg_dump dumps the stuff in public
even though that stuff seems to be created, and
therefore present, in every database I create on a
given server instance?  Isn't that duplication a waste
of space, and it's presence in the dump a waste of CPU
cycles?

Thanks again.

Ted
--- "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote:

> 
> pg_dump -x -O -s [databasename] > outfile.sql
> 
> HTH
>   Uwe


---(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] Query

[Ivan Sergio Borgonovo]

On Mon, 10 Dec 2007 12:23:49 + (GMT)
Ashish Karalkar <[EMAIL PROTECTED]> wrote:

> Hello there,
> I am having data in table something like below:
> 
> user_idtype_id
> 11
> 12
> 21
> 33
> 43
> 51
> 1   10
> 76
> 
> What i want is the count of all user group by type_id  who are
> subscribed to only one type e.g
> 
> type_id   count
> 12

  13

> 6 1

This is not to "only one type" or I didn't get the question.

Something like

create table pippo ( user_id int, type_id int);
insert into pippo values(1,1);
insert into pippo values(1,2);
insert into pippo values(2,1);
insert into pippo values(3,3);
insert into pippo values(4,3);
insert into pippo values(5,1);
insert into pippo values(1,10);
insert into pippo values(7,6);


select type_id, count(*) from pippo group by type_id having
count(*)=1;

10;1
6;1
2;1


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

Re: [GENERAL] Query

[Richard Huxton]

Ashish Karalkar wrote:

Hello there,
I am having data in table something like below:

user_idtype_id
11
12
21
33
43
51
1   10

76

What i want is the count of all user group by type_id  who are subscribed to 
only one type e.g


Part 1: Find users with only one type_id

SELECT user_id, max(type_id) as type_id
FROM user_types
GROUP BY user_id
HAVING count(*) = 1;

You could use min(type_id) instead of course, since the HAVING clause 
means there is only one type for each user-id.


Part 2: Summarise on type_id

SELECT type_id, count(*)
FROM
(
  SELECT user_id, max(type_id) as type_id
  FROM user_types
  GROUP BY user_id
  HAVING count(*) = 1
) AS users_with_one_type
GROUP BY type_id;

Note - not tested, might contain syntax errors

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Query

[Ashish Karalkar]

Hello there,
I am having data in table something like below:

user_idtype_id
11
12
21
33
43
51
1   10
76

What i want is the count of all user group by type_id  who are subscribed to 
only one type e.g

type_id   count
12
6 1

any suggestions?

Thanks in advance


With regards
Ashish




   
-
 Unlimited freedom, unlimited storage. Get it now

Re: [GENERAL] Problems with acessing xml functions on other database

[Richard Huxton]

x asasaxax wrote:

Hi everyone,

   I had the folowing problem: when i try to execute a xml_string function
on a database 'a' it works, but when i try this in database 'b' it doesen´t
works. Did anyone knows what its going on? Is that some kind of permission?


No-one can tell, you didn't provide the error message.

The first thing I'd do is find out where "xml_string" came from - I'm 
not aware of any built-in function called that.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Pg_catalog reference

[Richard Huxton]

Anton Nikiforov wrote:

Dear all,
i'm trying to create tables using pg_user (pg_authid) as a foreign key 
for my table. I need to log and control that only registered users can 
modify data and i want to control data changes via logging triggers. I 
need to know who exactly was modifying data. To be more exact i want to 
create a trigger that can log user information into the a table. I want 
to have a way to make rollbacks of high level data (documents...whatever).


But when i'm trying to create a constraint referencing pg_catalog tables 
- i'm getting an error.


Irritating, isn't it? You can't attach triggers to system tables, and 
that means no foreign-key references.



What you consider to be a solution in thi case?


The best I've come up with is to have an app_user table that you *can* 
have foreign keys referencing and have triggers on that keep pg_user 
up-to-date.


It's not perfect - as a sysadmin you can go in and delete pg_user rows 
while app_user assumes they're still there. In practice, it seems to 
work well enough though.


--
  Richard Huxton
  Archonet Ltd

---(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