Re: [GENERAL] Questions about large objects and the WAL

[Tom Lane]

Karen Ploski <[EMAIL PROTECTED]> writes:
> I'm trying to understand how large objects relate to
> the write-ahead log.

Large objects are just some data in a table.  The API for them is a bit
odd, but the reliability issues are not any different from any other
transaction.

> (1)When a large object is updated, how much of the
> large object is placed in the WAL buffers in shared
> memory by LogInsert?  

Every row you updated (which is any 2K segment of the
large object IIRC).

regards, tom lane

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

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

Re: [GENERAL] Data corruption zero a file - help!!

[Michael Fuhr]

On Tue, Feb 28, 2006 at 10:54:48PM -0700, Michael Fuhr wrote:
> Is your table really over 100G?  Anyway, if the block size is 8192
> then 902292 sould be in the .6 file.  If you can spare the time
> then you might run the dd and od commands that Tom Lane mentions
> in the above message and post the output.

pg_filedump is also handy for examining the backend's data files.

http://sources.redhat.com/rhdb/utilities.html

-- 
Michael Fuhr

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

Re: [GENERAL] Data corruption zero a file - help!!

[Michael Fuhr]

On Wed, Mar 01, 2006 at 04:12:53PM +1100, Noel Faux wrote:
> Now after doing some searches I managed to work out that the data 
> corruption starts at 902292.137
> using this sql:
> SELECT * FROM gap WHERE ctid = '(902292,$x)'
> Where $x I changed from 1-150.
> 
> as mentioned on this 
> post:http://archives.postgresql.org/pgsql-general/2005-11/msg01148.php
> 
> Following this post it seems all we need to do is re-zero from this 
> point on. However we're not sure which file to do this in.

This earlier message in that thread should be helpful:

http://archives.postgresql.org/pgsql-general/2005-11/msg01141.php

> I've worked out the database/relation files are
> $PGDATA/37958/111685332.* with the max * being 101.

Is your table really over 100G?  Anyway, if the block size is 8192
then 902292 sould be in the .6 file.  If you can spare the time
then you might run the dd and od commands that Tom Lane mentions
in the above message and post the output.  I think the command would
be

dd bs=8k skip=115860 count=1 if=$PGDATA/37958/111685332.6 | od -x

See Tom's message for how I arrived at .6 and 115860 (and verify
the math yourself).

-- 
Michael Fuhr

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

Re: [GENERAL] regarding grant option

[Michael Fuhr]

On Wed, Mar 01, 2006 at 09:50:24AM +0530, AKHILESH GUPTA wrote:
> just tell me the procedure that how can i grant all permissions for a
> database to any of the other user???
> i am using :-
> :->> grant all privileges on database  to ;
> GRANT
> :->> grant all ON DATABASE  to ;
> GRANT
> 
> and the query executes succesfully.
> but at the other user end, she is not able to access the database given in
> the above query.

Can the user connect to the database at all?  Is she getting an
error message?  If so, what's the error and at what point in the
connection is she getting it?  What did she do immediately prior
to getting the error?  Do the server logs contain any error messages?
If so, what are they?

If the user can't even connect then the problem is likely with
pg_hba.conf.  See "Client Authentication" in the documentation for
more information:

http://www.postgresql.org/docs/8.0/interactive/client-authentication.html

-- 
Michael Fuhr

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

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

[GENERAL] Data corruption zero a file - help!!

[Noel Faux]

Hi
all,

I posted this on the novice mailing list and as yet had no response,
hopefully someone here can help.

While we where trying to do a vacuum / pg_dump we encountered the
following error:


[EMAIL PROTECTED]:~$ pg_dumpall -d > dump.pg

pg_dump: dumpClasses(): SQL command failed

pg_dump: Error message from server: ERROR:  invalid page header in
block

9022921 of relation "gap"

pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor

pg_dumpall: pg_dump failed on database "monashprotein", exiting


Now after doing some searches I managed to work out that the data
corruption starts at 902292.137

using this sql: 
SELECT * FROM gap WHERE ctid = '(902292,$x)'

Where $x I changed from 1-150.


as mentioned on this post:http://archives.postgresql.org/pgsql-general/2005-11/msg01148.php


Following this post it seems all we need to do is re-zero from this
point on. However we're not sure which file to do this in.


I've worked out the database/relation files are

$PGDATA/37958/111685332.* with the max * being 101.


Any help locating which file we need to do the re-zero thing would be
really appreciated.


Cheers

Noel





begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:[EMAIL PROTECTED]
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard


---(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] regarding grant option

[AKHILESH GUPTA]

hi all
this is akhilesh from india.i just want to ask one thing regarding grant operation??
i am using pgsql 8.0 on ubuntu 5.10 linux.
just tell me the procedure that how can i grant all permissions for a database to any of the other user???
i am using :-
:->> grant all privileges on database  to ;
GRANT
:->> grant all ON DATABASE  to ;
GRANT

and the query executes succesfully.
but at the other user end, she is not able to access the database given in the above query.
here i have to grant permissions to that user individually for each and every table by using:
:->> grant ALL ON  to ;
GRANT

and all the permissions are granted to that user for that particular table.

i want same results for the entire database.
plz help me it's urgent

-- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064)  (+911744293789)"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"

Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Roman Neuhauser replied to me:
>> I suspect that the listserv is choking on the "#" symbol. This is
>> undoubtably a bug in their software. For one thing, it should not
>> have accepted the email address as far along as it did.

> Eh? That email address is completely valid.

Sure it is, but the mailing list software doesn't think so, or at least
part of it does. What I meant by the above was that if the software was
going to choke on the email address, it should have done so right away,
instead of allowing it to get fully subscribed and then deciding that
it was not valid at some other point in the workflow leading up to
actually sending you some email.

>> Well, all I can recommend then is perhaps trying an email address without
>> the "#" sign if possible.
  
> Funny thing, I've managed to sign to pgsql-general with the address
> displayed above when I sent the very first subscribe command from
> that address. That suggests that the bug shows up

The address is fully subscribed to the list, both Marc and I can verify
that. The problem is that it fails some other check when mail is sent
to it. Why in the world that logic is not centralized, I cannot imagine.

Marc wrote:
> Nope, that isn't it .. the thing is, from this end, I can't see/find any
> problems ... he's subscribed to the list:

Marc, I was able to view that weird whitespace message by going to
"Subcribers", entering "neuhauser" in the search box, and then "clicking"[1]
on the troublesome name in question.

[1] I actually use links, so there is no mouse clicking involved, but I've
never been able to find a better verb to use there. :)

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200602282257
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFEBRxDvJuQZxSWSsgRAjfbAJ4oowDAt5sweySUv+EsL476bmXj0ACfVDM9
1TAipT8ucu9yc2jODt8zcCU=
=/Rql
-END PGP SIGNATURE-



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

Re: [GENERAL] Full Text Indexing and Syntax

[Joshua D. Drake]

EXPLAIN
SELECT t1.id, t2.id
FROM test1 t1, test2 t2
WHERE lower( t1.keyword ) ~ ( lower ( '^' || t2.article ));


It doesn't appear that you're using tsearch2. PostgreSQL does not 
include full text search in the basic installation. Have you installed 
tsearch2?

Which is included in the PostgreSQL contrib

Joshua D. Drake



Michael Glaesemann
grzm myrealbox com




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




--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/


---(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] Full Text Indexing and Syntax

[Michael Glaesemann]

On Mar 1, 2006, at 6:54 , flood wrote:


Unfortunately I can not seem to get my query to use PG's full text
indexing, it keeps doing a seq scan:




EXPLAIN
SELECT t1.id, t2.id
FROM test1 t1, test2 t2
WHERE lower( t1.keyword ) ~ ( lower ( '^' || t2.article ));


It doesn't appear that you're using tsearch2. PostgreSQL does not  
include full text search in the basic installation. Have you  
installed tsearch2?


Michael Glaesemann
grzm myrealbox com




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

[GENERAL] Questions about large objects and the WAL

[Karen Ploski]

I'm trying to understand how large objects relate to
the write-ahead log.  I've read the following sections
in the 8.1 user guide:

Chapter 26 Reliability and the Write-Ahead Log
Chapter 29 Large Objects
Section 42.21 pg_lageobject

I have some naive questions

(1)When a large object is updated, how much of the
large object is placed in the WAL buffers in shared
memory by LogInsert?  
  (a) None
  (b) Just the page (or pages) in pg_largeobject that
changed, or 
  (c) the entire large object?

(2) Are the WAL buffer(s) that contain part (or all)
of the large object written to the log file, on
permanent storage?

(3) Section  E.1.3.12. pg_dump Changes in the 8.1
documentation describes a change to pg_dump:

• Allow pg_dump to dump large objects even in text
mode (Tom)
With this change, large objects are now always dumped;
the former -b switch is a no-op.

Does pg_dump take the large objects from the shared
WAL buffers, or the log file (or both)?


I have similar questions about TOAST fields.  I assume
that TOAST fields aren't "stored" in pg_largeobject,
they are not "broken into pages" like large objects. 
Apart from that, 

(4) are TOAST fields written to the log file, on
permanent storage?
(5) does pg_dump dump TOAST fields (8.1 and later)?

Thank you!
Karen

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(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] Full Text Indexing and Syntax

[flood]

Hi folks, I am having some trouble with this query that should be using
FTI.  There are 2 tables, one with a list of keywords and the other
containing a body of articles.

I am trying to get a query to return the IDs of each keyword with the
ID of each article that contains that keyword.

So the 2 tables are:

test1:
 ->id
 ->keyword

test2:
 ->id
 ->article

Unfortunately I can not seem to get my query to use PG's full text
indexing, it keeps doing a seq scan:

EXPLAIN
SELECT t1.id, t2.id
FROM test1 t1, test2 t2
WHERE lower( t1.keyword ) ~ ( lower ( '^' || t2.article ));
-
 Nested Loop  (cost=20.00..30040.00 rows=5001 width=8)
   Join Filter: (lower("outer".keyword) ~ lower(('^'::text ||
"inner".article)))
   ->  Seq Scan on test1 t1  (cost=0.00..20.00 rows=1000 width=36)
   ->  Materialize  (cost=20.00..30.00 rows=1000 width=36)
 ->  Seq Scan on test2 t2  (cost=0.00..20.00 rows=1000
width=36)


Could someone explain how I can restructure this SQL to use the index,
or perhaps suggest a workaround?  

I am using PG 7.4.7 on Debian 3.1.


---(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] Looking for a fix to index bloat

[bfraci]

We are suffering from the same issue that is described in this email thread http://archives.postgresql.org/pgsql-general/2005-07/msg00486.php.  
 
 
I don't know if this is the appropriate place to make this request, so if not, please forgive me.  However, in our particular case, we don't have enough disk space nor money to allow the indexes to grow to a steady state.  We are willing to pay someone to implement the suggested fix described in the above thread on version 8.0.3.  If interested, please respond to this email with your contact information and a rough idea of how much it would cost and how long it would take to get the job done.
 
Thank you for your consideration.
 
Brent
 

Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?

[Jim C. Nasby]

On Tue, Feb 28, 2006 at 07:30:58PM -0500, Tom Lane wrote:
> "Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> >> Actually, I suspect it's puking on the #'s in the email addresses.
> 
> > Nope, that isn't it .. the thing is, from this end, I can't see/find any 
> > problems ... he's subscribed to the list:
> 
> > %mj_shell -p  who pgsql-general | grep -i sigpipe.cz
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> 
> Is it possible that majordomo is pushing out the messages just fine, but
> then something downstream (ie one of the forwarders) is puking on the
> funny addresses?  Not sure why you'd not be seeing bounce-backs if that
> were it, but ...

Another possibility is that the subscription code in majordomo has a
different idea on valid emails than the code that accepts emails from
members...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

   http://archives.postgresql.org

Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?

[Tom Lane]

"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
>> Actually, I suspect it's puking on the #'s in the email addresses.

> Nope, that isn't it .. the thing is, from this end, I can't see/find any 
> problems ... he's subscribed to the list:

> %mj_shell -p  who pgsql-general | grep -i sigpipe.cz
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]

Is it possible that majordomo is pushing out the messages just fine, but
then something downstream (ie one of the forwarders) is puking on the
funny addresses?  Not sure why you'd not be seeing bounce-backs if that
were it, but ...

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] majordomo unmaintained, postmaster emails ignored?

[Marc G. Fournier]

Nope, that isn't it .. the thing is, from this end, I can't see/find any 
problems ... he's subscribed to the list:


%mj_shell -p  who pgsql-general | grep -i sigpipe.cz
  [EMAIL PROTECTED]
  [EMAIL PROTECTED]

In fact, he's been registered since Jul '05:

  Address: [EMAIL PROTECTED]
Address is valid.
  Address Mailbox: [EMAIL PROTECTED]
Registered as[EMAIL PROTECTED]
Registered onWed Jul 27 06:44:37 2005
Data last changed on Mon Feb 27 09:20:21 2006
Subscribed to1 lists

  pgsql-general:
Subscribed as   [EMAIL PROTECTED]
Subscribed on   Mon Feb 27 09:20:21 2006
Last changed on Mon Feb 27 09:20:21 2006
Receiving   each message as it is posted
Subscriber flags:
  noeliminatecc
  nohide
  nohidepost
  nopostblock
  prefix
  noreplyto
  selfcopy
  norewritefrom
  noackstall
  noackdeny
  noackpost
  noackreject

So I'm not 100% certain *what* the problem is :(

On Tue, 28 Feb 2006, Jim C. Nasby wrote:


On Tue, Feb 28, 2006 at 03:14:39PM -, Greg Sabino Mullane wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


It looks like the listserv cannot handle that address - it says:

"Individual words are not allowed in an e-mail address without an
intervening period or at symbol ('.' or '@')."

Perhaps you could simply use your normal email address, and filter
on the "List-ID" header? (Assuming you are setting up that expanded
email address to help with filtering). Feel free to email me offlist
as well.


Actually, I suspect it's puking on the #'s in the email addresses.
--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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




Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?

[Jim C. Nasby]

On Tue, Feb 28, 2006 at 03:14:39PM -, Greg Sabino Mullane wrote:
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> 
> It looks like the listserv cannot handle that address - it says:
> 
> "Individual words are not allowed in an e-mail address without an
> intervening period or at symbol ('.' or '@')."
> 
> Perhaps you could simply use your normal email address, and filter
> on the "List-ID" header? (Assuming you are setting up that expanded
> email address to help with filtering). Feel free to email me offlist
> as well.

Actually, I suspect it's puking on the #'s in the email addresses.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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

Re: [GENERAL] ltree + gist index performance degrades significantly over a night

[CG]

--- Bernhard Weisshuhn <[EMAIL PROTECTED]> wrote:

> On Mon, Feb 27, 2006 at 10:27:20AM -0800, CG <[EMAIL PROTECTED]> wrote:
> 
> > [...] I'd need to see if the space required for the varchar+btree tables
> are
> > comparible, better, or worse than the ltree+gist tables with regards to
> size.
> 
> Please test this, I'm guessing (hoping actually) that having bazillions of
> combinations of 26 (or so) characters (ltree labels) might be consuming
> less space than having bazillions of substings in the database.
> 
> Or maybe some clever combination of both approaches?
> 
> If you find out something interesting, please let me know.

Performance using varchar+btree, breaking up the string into distinct letter
groups >= 3 chars is slightly better. Size of the varchar search vector table
table is much bigger.. Most of my fields are about 15-25 characters in length.
Expect even bigger tables for longer fields. The size of the btree index is
less. The time to bootstrap the data into the tables was significantly longer.
I used two triggers, one that normalized the search field before insert, and
another that inserted a breakdown row after the insert row. There's a recursive
effect built-in to get down to the smallest unique element.

I'm sticking with ltree and setting up a vacuum analyze on a cron to keep the
searches snappy. Hope that helps you with your project!

CG

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

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

Re: [GENERAL] Size comparison between a Composite type and an

[Martijn van Oosterhout]

On Tue, Feb 28, 2006 at 09:51:54PM +0100, Denis Gasparin wrote:
> Hi Doug.
> 
> I considered also the numeric type. In that case if the number is of 32 
> digits the storage size is of 2*8 + 8 = 24 bytes.
> If i store it using a composite data type of two bigints the size is 2*8 
> + composite data structure overhead bytes.
> 
> If the composite data type has 4 bytes overhead, I save 4 bytes for each 
> number... that is important because I must store many many numbers.

Well, that's an assumption. There was a time where composite types had
a 10+ byte overhead. I'm not sure what the current overhead is but I'm
fairly sure it's more than 4.

> Performance speaking, the numeric type can be indexed?

Ofcourse.

> In the case of composite data types, I must create an operator class for 
> indexing the fields of that type...
> What is the performance gap between indexed numeric and composite?

I think numeric will win easily. a few other things:

1. Alignment will probably eat any small savings you make
2. Numeric will take less space for small numbers
3. You have to make functions to index these composite type. Unless you
write them directly in C, the numeric code will win just on the
overhead of whatever other language you use to do it.

The best performance might come from making your own int16 type but all
this seems like premature optimisation to me. How about you start with
numeric and optimise when you see there's an actual problem.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] Size comparison between a Composite type and an

[Douglas McNaught]

Denis Gasparin <[EMAIL PROTECTED]> writes:

> If the composite data type has 4 bytes overhead, I save 4 bytes for
> each number... that is important because I must store many many
> numbers.

Yes, if size is a big issue you might be better off with a specialized
type.

-Doug

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

Re: [GENERAL] Size comparison between a Composite type and an

[Denis Gasparin]

Hi Doug.

I considered also the numeric type. In that case if the number is of 32 
digits the storage size is of 2*8 + 8 = 24 bytes.
If i store it using a composite data type of two bigints the size is 2*8 
+ composite data structure overhead bytes.


If the composite data type has 4 bytes overhead, I save 4 bytes for each 
number... that is important because I must store many many numbers.


Performance speaking, the numeric type can be indexed?
In the case of composite data types, I must create an operator class for 
indexing the fields of that type...

What is the performance gap between indexed numeric and composite?

Thank you,
Denis


Douglas McNaught wrote:

[EMAIL PROTECTED] writes:

  

I need to store very large integers (more of 30 digits).



Er,

What's wrong with the NUMERIC type?  That can go up to hundreds of
digits.

-Doug

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

  



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

Re: [GENERAL] problem with windows xp sp2 and postgres-8.1.3

[Magnus Hagander]

> first of all, great effort from you this tool.
>  
> Problem exists with following config:
> - Windows XP Pro, SP2 (English).
> - PostgreSQL-8.1.3
> - lot of development programs, and IDE-s (for the clients i 
> am working unfortunately they are windoz-based :-(...).
>  
> Same problem occurs as described here
> and here 
>  .
> =
> "could not create inherited socket: error code 10022".
> =

This sounds a lot like broken firewall, broken antivirus, broken
antispyware or such software. Take a look with
http://www.cexx.org/lspfix.htm to see what you have there - most likely
at least one broken LSP.


> As i tried again manually(postmaster...), had the same 
> problem. I turned off my firewall services, windows security 
> center firewall and antivirus check (automatic updates are 
> on), but i got the same error.

Turn off is often not enough, you need to uninstall.

> Can it have anything related to windows-socket bind problems 
> with Windows-XP-SP2 integrated firewalls however it is disabled?

No, the intergrated firewall should work fine, as long as it's not
blocking the connections (meaning if it's disabled, it shouldn't be a
problem). It's known not to have the LSP bug.

//Magnus

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

   http://archives.postgresql.org

Re: [GENERAL] PQisBusy returns true but no more data is received.

[Tom Lane]

Pelle Johansson <[EMAIL PROTECTED]> writes:
> Basically, I have an epoll loop that executes the following code when  
> I receive data from postgresql (greatly simplified).
> ...
> The problem is that after two iterations in the loop PQisBusy()  
> returns true, making me exit to the event loop, but no more data is  
> received, so the function will not be called again.

Shouldn't happen.  Can you provide a complete test case?  (Are you sure
the problem isn't just the SELECT taking a long time?)

> Is it safe to move the call to PQisBusy() outside the loop,

Not if the idea is to not block in PQgetResult.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Breaking Path/Polygon Data into Pieces

[Volkan YAZICI]

On Feb 27 03:10, Michael Fuhr wrote:
> PostGIS has geometry accessors that might work.  You'd need to be
> using PostGIS geometry types instead of the PostgreSQL types.
> 
> http://postgis.refractions.net/docs/ch06.html
> 
> Are the following examples anything like what the user in tr-general
> was looking for?

Yes. As I understand from your examples, they're quite efficient for
related purposes I mentioned. But wouldn't it worth adding a simple
part() function for this? Because, with this feature, people won't need
to use another project for such a basic operation on path and polygon
types which are supported by PostgreSQL by default.


Regards.

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

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

[GENERAL] PQisBusy returns true but no more data is received.

[Pelle Johansson]

Hello list,

I'm new here, but didn't see the problem in the archives.

Basically, I have an epoll loop that executes the following code when  
I receive data from postgresql (greatly simplified).


int read_sql (PGconn *conn)
{
  PGnotify *notice;
  PGresult *res;

  if (!PQconsumeInput (conn))
return -1;

  while (1)
  {
while ((notice = PQnotifies (conn)))
{
  handle_notice (notice);
  PQmemfree (notice);
}

if (PQisBusy (conn))
  return 0;

res = PQgetResult (conn);
if (!res)
  break;

while ((notice = PQnotifies (conn)))
{
  handle_notice (notice);
  PQmemfree (notice);
}

handle_result (res);
PQclear (res);
  }

  handle_query_done (conn);
}

(I've not tried to compile this sample code.)
The SQL query in question is a BEGIN followed by a SELECT (sent in  
the same PQsendQuery()).


The problem is that after two iterations in the loop PQisBusy()  
returns true, making me exit to the event loop, but no more data is  
received, so the function will not be called again. If I disable the  
call to PQisBusy() everything works as expected (PQgetResult() will  
return NULL on the third iteration). If I move the call to PQisBusy()  
outside the loop, everything also works good.


Is it safe to move the call to PQisBusy() outside the loop, or is it  
possible that PQgetResult() will block on a long SELECT in that case?  
(that would be very bad for me). Or have I misunderstood something  
about these functions?

--
Pelle Johansson


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

Re: [GENERAL] How many postmasters should be running?

[Tom Lane]

"Stock, Stuart" <[EMAIL PROTECTED]> writes:
> Perhaps I'm just seeing a moment-in-time snapshot of the postmaster
> fork()'ing to handle these connections, but because they were rejected, it
> never had time to rename itself to 'postgres'?

There's definitely a short window between the fork and the point where
the child process is able to change the way it appears in ps.
[ eyes code... ]  In particular, if you have log_hostname enabled,
it looks like we could wait for a DNS response (to the lookup of the
client IP address) before we change the ps status.

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] How many postmasters should be running?

[Stock, Stuart]

Here's ps output, as you can see there is a second postmaster (pid 17303)
that is a child of the original postmaster (pid 28317): 

prod 28317 1  0 Feb25 ?  00:00:02
/mnt/prod/postgresql-8.1.2/bin/postmaster -i
prod 28321 28317  0 Feb25 ?  00:00:11 postgres: logger process
prod 28323 28317  0 Feb25 ?  00:02:32 postgres: writer process
prod 28324 28317  0 Feb25 ?  00:00:00 postgres: archiver process
prod 28325 28317  0 Feb25 ?  00:00:05 postgres: stats buffer process
prod 28326 28325  0 Feb25 ?  00:00:11 postgres: stats collector process
prod 13571 28317  0 09:19 ?  00:00:00 postgres: pos abdb host.x.y.net(33623)
idle
prod 16214 28317  5 10:05 ?  00:00:46 postgres: pos abdb 10.123.45.79(4232)
idle
prod 16268 28317  0 10:05 ?  00:00:00 postgres: pos abdb 10.123.45.79(4237)
idle
prod 16328 28317  0 10:06 ?  00:00:05 postgres: pos xyzdb 10.123.45.79(4245)
idle
prod 17303 28317  0 10:19 ?  00:00:00
/mnt/prod/postgresql-8.1.2/bin/postmaster -i

Viewing this as a tree (ps aefx) makes it a little more obvious:

28317 ?  S 0:02 /mnt/prod/postgresql-8.1.2/bin/postmaster -i HOSTNAME=host1
TERM=xterm SHE
28321 ?  S 0:11  \_ postgres: logger process
28323 ?  S 2:32  \_ postgres: writer process
28324 ?  S 0:00  \_ postgres: archiver process
28325 ?  S 0:05  \_ postgres: stats buffer process
28326 ?  S 0:11  |   \_ postgres: stats collector process
13571 ?  S 0:00  \_ postgres: pos abdb host.x.y.net(33623) idle
16214 ?  S 0:46  \_ postgres: pos abdb 10.123.45.79(4232) idle
16268 ?  S 0:00  \_ postgres: pos abdb 10.123.45.79(4237) idle
16328 ?  S 0:05  \_ postgres: pos xyzdb 10.123.45.79(4245) idle
17327 ?  S 0:00  \_ /mnt/prod/postgresql-8.1.2/bin/postmaster -i
HOSTNAME=host1 TERM=xterm

Wait a sec...looking at the above I just noticed that the process id of the
second postmaster in the first 'ps' output listing is different from the pid
in the tree listing. At the time this second postmaster appeared, there were
a lot of connections to the database being attempted (and rejected due to
pg_hba.conf configuration). 

Perhaps I'm just seeing a moment-in-time snapshot of the postmaster
fork()'ing to handle these connections, but because they were rejected, it
never had time to rename itself to 'postgres'?

Stuart

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 27, 2006 10:09 PM
To: Michael Fuhr
Cc: Stock, Stuart; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How many postmasters should be running? 

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Mon, Feb 27, 2006 at 09:05:51PM -0500, Stock, Stuart wrote:
>> A few minutes ago, we were surprised to find a second postmaster process
>> running on our database machine as a child of the original postmaster.

> Each connection causes the postmaster to fork a new process to
> handle that connection.

Also, all postmaster child processes will properly identify themselves
as long as you are using the appropriate ps options.  (Depending on your
OS, the default ps output format might just list them all as "postmaster".)
Try something like "ps auxww | grep postgres" if using Linux.

regards, tom lane


If you have received this e-mail in error or wish to read our e-mail disclaimer 
statement and monitoring policy, please refer to 
http://www.drkw.com/disc/email/ or contact the sender.


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

Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-02-28 15:14:39 -:
> It looks like the listserv cannot handle that address - it says:
> 
> "Individual words are not allowed in an e-mail address without an
> intervening period or at symbol ('.' or '@')."
 
Which address? I see no whitespace in any of the addresses displayed
in my email.

> Perhaps you could simply use your normal email address, and filter
> on the "List-ID" header? (Assuming you are setting up that expanded
> email address to help with filtering). Feel free to email me offlist
> as well.

I'm switching away from header-based filtering because that breaks
from time to time because of version and/or configuration changes in
the software involved.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(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] Problem with PostgreSQL 8.1.3 on Windows XP Pro

[Tom Lane]

"Ets ROLLAND" <[EMAIL PROTECTED]> writes:
> 2006-02-28 15:36:52 WARNING:  corrupted pgstat.stat file
> 2006-02-28 15:36:52 LOG:  corrupted pgstat.stat file

If this happened once, immediately after an update from a pre-8.1.3
version, then it's expected and you can ignore it.  If it's happening
repeatably then there is cause for worry.

regards, tom lane

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

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

Re: [GENERAL] implicit tables syntax disappeared from 8.0->8.1

[Tino Wildenhain]

A. Kretschmer schrieb:

am  28.02.2006, um 14:01:44 +0100 mailte [EMAIL PROTECTED] folgendes:


update   t1
set  t1f1='test'
where  t1.t1f2=t2.t2f2
and  t1.t1f3=t2.t2f3;

unfortunately, now I get the error that t2 is not in the FROM clause.



You can set 


add_missing_from

in yout postgresql.conf, but please read
http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION


Better dont do that but use the FROM clause of update:

http://www.postgresql.org/docs/8.1/static/sql-update.html

e.g.

UPDATE t1
   SET t1.f1='test'
FROM t2
WHERE t1.t1f2=t2.t2f2
AND t1.t1f3=t2.t2f3;

(although I bet your example is sloppy and you even want t1, t2 and t3 -
so add t3 to the FROM list as you know with SELECT)

HTH
Tino



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

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

Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


It looks like the listserv cannot handle that address - it says:

"Individual words are not allowed in an e-mail address without an
intervening period or at symbol ('.' or '@')."

Perhaps you could simply use your normal email address, and filter
on the "List-ID" header? (Assuming you are setting up that expanded
email address to help with filtering). Feel free to email me offlist
as well.

- --
Greg Sabino Mullane [EMAIL PROTECTED]  [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200602281003
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFEBGd8vJuQZxSWSsgRAs6CAJ47kzmmGT3op8KT4cjEjQ3oSN5//ACgy7RQ
Kdl0PPLLE33r6Ytb0AQ+FC8=
=+5r/
-END PGP SIGNATURE-



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

Re: [GENERAL] Dumping functions

[Bricklen Anderson]

Steve Crawford wrote:

How can I dump a function definition with pg_dump?

Background: We often need to create objects that are all relevant to 
only a specific project. Sometimes it is a single table. Other times 
there are many tables, indexes, views, rules, triggers and functions. 
All the objects share a unique substring that identifies the project so 
automatically creating the list is easy.


When I use pg_dump to dump a table I will by default also get the 
associated indexes, rules and triggers. Views can be dumped just like 
tables. So all I need to do to archive the whole mess is to automate the 
dump of the functions.


Ideas?

Cheers,
Steve



This thread has a few options available
http://archives.postgresql.org/pgsql-general/2005-10/msg01589.php

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

[GENERAL] Problem with PostgreSQL 8.1.3 on Windows XP Pro

[Ets ROLLAND]

Hello,
 
I have an XP Pro Box, 1 Go RAM, 160 Go HD, with 
PostgreSQL 8.1.3 win32.
The service pgsql-8.1 appear to be 
started,
but when I try to connect with PgAdmin 
III 1.4.1, it hangs and I need to terminate the program.
The pg_log gave :
2006-02-28 15:36:52 LOG:  database system was 
shut down at 2006-01-30 10:43:05 Paris, Madrid2006-02-28 15:36:52 LOG:  
checkpoint record is at 0/C424CFE02006-02-28 15:36:52 LOG:  redo record 
is at 0/C424CFE0; undo record is at 0/0; shutdown TRUE2006-02-28 15:36:52 
LOG:  next transaction ID: 150552; next OID: 352912006-02-28 15:36:52 
LOG:  next MultiXactId: 1; next MultiXactOffset: 02006-02-28 15:36:52 
LOG:  database system is ready2006-02-28 15:36:52 LOG:  
transaction ID wrap limit is 2147484148, limited by database 
"postgres"2006-02-28 15:36:52 WARNING:  corrupted pgstat.stat 
file2006-02-28 15:36:52 LOG:  corrupted pgstat.stat 
file
It seems that pgstat.stat would be corrupted, what 
can I do ?
Best regards.
 
Luc
BEGIN:VCARD
VERSION:2.1
N:ROLLAND;Ets
FN:Ets ROLLAND
ORG:Ets ROLLAND
TEL;WORK;VOICE:04 66 81 56 62
TEL;CELL;VOICE:06 11 18 98 88
TEL;WORK;FAX:04 66 04 03 42
ADR;WORK:;;13 Grand'rue;Clarensac;Gard;30870;France
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:13 Grand'rue=0D=0AClarensac, Gard 30870=0D=0AFrance
EMAIL;PREF;INTERNET:[EMAIL PROTECTED]
REV:20060228T151212Z
END:VCARD

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

Re: [GENERAL] implicit tables syntax disappeared from 8.0->8.1

[A. Kretschmer]

am  28.02.2006, um 14:01:44 +0100 mailte [EMAIL PROTECTED] folgendes:
> update   t1
> set  t1f1='test'
> where  t1.t1f2=t2.t2f2
> and  t1.t1f3=t2.t2f3;
> 
> unfortunately, now I get the error that t2 is not in the FROM clause.

You can set 

add_missing_from

in yout postgresql.conf, but please read
http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION


HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47215,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

   http://archives.postgresql.org

[GENERAL] implicit tables syntax disappeared from 8.0->8.1

[pg]

Hello,

the following used to work:

create table t1(t1f1 text, t1f2 text, t1f3 text);
create table t2(t2f2 text, t2f3 text);

insert ...

update   t1
set  t1f1='test'
where  t1.t1f2=t2.t2f2
and  t1.t1f3=t2.t2f3;

unfortunately, now I get the error that t2 is not in the FROM clause.

I know I can do

update t1
set t1f1='test'
where t1.t1f2||'/'||t1.t1f3 in
(select t2.t2f2||'/'||t2.t2f3 from t2);

But I'm afraid that's very expensive.  Do you have a suggestion for
alternative syntax for my initial query?

Thank you,

Oliver Seidel



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

Re: [GENERAL] indexes

[Martijn van Oosterhout]

On Tue, Feb 28, 2006 at 07:56:14AM -0700, Chethana, Rao (IE10) wrote:
> Hello!
> 
> i tried creating indexes on columns that have datatype as bigint  or
> smallint , but  when I performed explain analyze,  the query was using
> sequential scan 
> 
>  instead of index scanning.
> 
> Is it not possible to create indexes for attributes whose datatypes  r
> bigint or smallint ?

On older versions of PostgreSQL you had to quote the integers to get it
to use an index scan. Recent versions don't have this problem anymore.
It could also have to do with the size of your table. However, since
you don't provide any useful details, I'm just speculating.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

[GENERAL] indexes

[Chethana, Rao (IE10)]

Hello!

 

i tried creating indexes on columns that have datatype as
bigint  or smallint , but  when I performed explain analyze,  the query was
using sequential scan 

 instead of index scanning.

 

Is it not possible to create indexes for attributes whose
datatypes  r   bigint or smallint ?

 

Re: [GENERAL] Temporal Databases, offtopic - relative updates

[Christopher Browne]

In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] 
("[EMAIL PROTECTED]") transmitted:
> I have a somehow related question to this topic: is it possible to
> know (in postgresql) if an update on a column is absolute (set col =
> 3) or relative to it's previous value (set col = col + 3)
> in a trigger one have access to OLD row values and NEW row values, but
> no correlation between the two. is this type of information available
> somewhere in postgresql ?

No, that's not available in any direct fashion.

I understand that in some replication systems (Sybase has been the
name bandied about in this context), you can mark particular table
columns as being ones where "deltas" should be computed.

In effect, you note down somewhere that certain columns represent
"balances," which implies that changes should always be regarded as
"deltas."

It seems like it ought to work...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "gmail.com")
http://cbbrowne.com/info/x.html
If you're not part of the solution, you're part of the precipitate.

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

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

Re: [GENERAL] Sequences/defaults and pg_dump

[Nikolay Samokhvalov]

On 2/7/06, Martijn van Oosterhout  wrote:
> On Tue, Feb 07, 2006 at 03:28:31PM +0300, Nikolay Samokhvalov wrote:
> > The real situation would be as the following.
> > I want to use some algorithm to hide real number of registered users
> > in my table user. So, I don't want to use simple sequence, when every
> > new registered user in my system can guess what is the number of
> > registered users simply observing his ID. So, I use following
> > algorithm:
> > (nextval('...name of the sequnence...') * N) mod % M,
> > where N and M are quite big numbers that have no common multiples
> > besides 1 (sorry, do not remember the English term for those numbers
> > ;-) ).
>
> Even then you could do it by saying:
>
> ALTER SEQUENCE x MAXVALUE M INCREMENT N CYCLE;
>
it's a pity, but no, I can't :-(
after reaching MAXVALUE sequence starts with MINVALUE (1 by default)...
for example with following sequence:

test=#  CREATE SEQUENCE testseq INCREMENT BY 3 MAXVALUE 10 CYCLE;

...I always obtain only 1, 4, 7 and 10... ;-(

--
Best regards,
Nikolay

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

Re: [GENERAL] Size comparison between a Composite type and an

[Douglas McNaught]

[EMAIL PROTECTED] writes:

> I need to store very large integers (more of 30 digits).

Er,

What's wrong with the NUMERIC type?  That can go up to hundreds of
digits.

-Doug

---(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] majordomo unmaintained, postmaster emails ignored?

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-02-27 16:32:55 -0400:
> On Mon, 27 Feb 2006, Roman Neuhauser wrote:
> 
> ># [EMAIL PROTECTED] / 2006-02-26 19:01:58 -0400:
> >>'k, I just checked all the lists you listed, and you are subscribed to
> >>each of them ... are you not receiving messages?

(...)
 
> Can you try something more recent then "last year"?

Here's a copy of an email I sent you off list documenting
stalled-for-approval requests from yesterday.

: Date: Mon, 27 Feb 2006 15:43:54 +0100
: From: Roman Neuhauser <[EMAIL PROTECTED]>
: To: "Marc G. Fournier" <[EMAIL PROTECTED]>
: Subject: Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
: Message-ID: <[EMAIL PROTECTED]>
: References: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
: Mime-Version: 1.0
: Content-Type: text/plain; charset=us-ascii
: Content-Disposition: inline
: In-Reply-To: <[EMAIL PROTECTED]>
: User-Agent: Mutt/1.5.9i
: Status: RO
:
: Note: I've removed pgsql-general@ from the recipient list so as to
: reduce clutter. Feel free to add it again for you reply if you feel
: this is relevant on that list.
:
: # [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400:
: > On Sun, 26 Feb 2006, Roman Neuhauser wrote:
: > >I've been waiting five months for the majordomo moderators to approve
: > >my subscription requests to several @postgresql.org mailing lists.
: >
: > the majordomo moderators don't have to approve subscribe requests
:
: The above is false under *certain circumstances*. Marc, can you
: tell me what attribute(s) of these requests cause(s) majordomo to
: wait for approval?
:
: I've just had this exchange with [EMAIL PROTECTED] (only the hackers
: request is put here for brevity):
:
: : Date: Mon, 27 Feb 2006 15:28:18 +0100
: : From: Roman Neuhauser <[EMAIL PROTECTED]>
: : To: [EMAIL PROTECTED]
: :
: : subscribe-set pgsql-docs noprefix [EMAIL PROTECTED]
: : subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED]
: : subscribe-set pgsql-performance noprefix [EMAIL PROTECTED]
: : subscribe-set pgsql-sql noprefix [EMAIL PROTECTED]
:
: : Date: Mon, 27 Feb 2006 10:28:18 -0400
: : From: [EMAIL PROTECTED]
: : To: Roman Neuhauser <[EMAIL PROTECTED]>
: : Subject: Majordomo results
: :
: :
: :  subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED]
: :  The subscribe command did not succeed.
: : 
: :  The request
: :    "subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED]"
: :  must be confirmed by
: :    [EMAIL PROTECTED]
: :  and approved by the moderators.  Confirmation instructions have 
been
: :  mailed in a separate message.
: : 
:
: : Date: Mon, 27 Feb 2006 10:28:17 -0400
: : From: [EMAIL PROTECTED]
: : To: [EMAIL PROTECTED]
: : Subject: BC9A-BD57-4C44 : CONFIRM from pgsql-hackers (subscribe)
: :
: : __
: : The following request
: :
: :   "subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED]"
: :
: : was sent to
: : by Roman Neuhauser <[EMAIL PROTECTED]>.
: :
: : To accept or reject this request, please do one of the following:
: (...)
:
: : Date: Mon, 27 Feb 2006 15:29:11 +0100
: : From: Roman Neuhauser <[EMAIL PROTECTED]>
: : To: [EMAIL PROTECTED]
: : Subject: Re: BC9A-BD57-4C44 : CONFIRM from pgsql-hackers (subscribe)
: :
: : accept BC9A-BD57-4C44
:
: : Date: Mon, 27 Feb 2006 10:29:13 -0400
: : From: [EMAIL PROTECTED]
: : To: Roman Neuhauser <[EMAIL PROTECTED]>
: : Subject: Majordomo results: Re: BC9A-BD57-4C44 : CONFIRM from pgsql-
: :
: :
: :  accept BC9A-BD57-4C44
: :  The accept command for token BC9A-BD57-4C44 succeeded,
: :  but further approval is needed.
: : 
: :  Now the request must be approved by the moderators.
: :  The results will be mailed to you after this is done.
: : 
: : 
: :
: : Valid commands processed: 1
: : 0 succeeded, 1 stalled, and 0 failed.
: :
: :
: : Use the following command:
: :   sessioninfo 530ce04b8a9f02fee27a58acb99d9cb88a092ae2
: : to see technical information about this session.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

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

Re: [GENERAL] Temporal Databases, offtopic - relative updates

[[EMAIL PROTECTED]]

hello,

I have a somehow related question to this topic: is it possible to know 
(in postgresql) if an update on a column is absolute (set col = 3) or 
relative to it's previous value (set col = col + 3)
in a trigger one have access to OLD row values and NEW row values, but 
no correlation between the two. is this type of information available 
somewhere in postgresql ?


thanks,
Razvan Radu

Rodrigo Sakai wrote:

  Hi everyone,
 
  I'm focus on temporal databases (not temporary), and I want to know 
if anyone here is studying this tecnologies too. So, we can exchange 
knowlegment. Specifically, anyone who is trying to implement on 
postgresql the aspect of time (temporal).
  These researches are lead by Richard Snodgrass. So, anyone who have 
something to share, please contact me!
 
  Thanks!!!






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

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

[GENERAL] Size comparison between a Composite type and an equivalent Text field

[denis]

I need to store very large integers (more of 30 digits).

I found two solutions to this problem:
- using a text field
- splitting the integer into 2 parts and then storing them in a 
composite type with 2 bigint fields


The definitive choice will depend on the disk space used by one solution 
instead of the other.


I think the storage size of the text field will be the number of the 
digits plus some extra data for the structure of the text type.
For the composite type the size will be of 2*8 bytes plus the extra data 
for maintaning the structure of the type.


For example if i have an integer of 30 digits:

Text Field
30 bytes + sizeof(text data structure)

Composite type
8 bytes + sizeof(composite data structure)

What of the two data structures (text or composite) will use more disk 
space?


Thank you,

--
Doct. Eng. Denis Gasparin
---
Edistar srl


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