Re: [GENERAL] setting and using variables in PSQL ????

[Nathan Wagner]

On Dec 14, 2007, at 9:19 PM, Gauthier, Dave wrote:

At the PSQL prompt, I want to set some variables based upon query  
results, or via static assignment, then insert a record with those  
values.  Sort of like...


select val1 into x from agedata where name = ‘joe_mako’;
select val12 into y from sizedata where name = ‘joe_mako’;
thename := ‘joe_mako’;
insert into newtable (name, age, height) values (thename,x,y);

Some sort of compound insert statement would solve my problem, but  
in a larger sense, how do you (can you ) do something like this  
inside a statement blocks of some kind?  I’ve done some PL-pgsql and  
know about statement blocks in that context.  But I don’t want to  
have to create a function, use it, then delete it.  Was wondering of  
something else could be done.


Do it all from inside the database...

granicus% cat ttab.sql
create temp table agedata (name text, val1 integer);
insert into agedata values ('joe_mako', 30);
create temp table sizedata (name text, val12 integer);
insert into sizedata values ('joe_mako', 200);

create temp table newtable as select
'joe_mako'::text as name,
(select val1  from agedata where name = 'joe_mako') as age,
(select val12 from sizedata where name = 'joe_mako') as size
;

select * from newtable;
granicus% psql -f ttab.sql
CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 1
SELECT
   name   | age | size
--+-+--
 joe_mako |  30 |  200
(1 row)

granicus%

but I probably wouldn't do it this way at all.  something like  
(untested)

select A.name, A.val1 as age, S.val12 as size
from agedata A left outer join sizedata S on S.name = A.name;

should work.

--
Nathan Wagner
[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

[GENERAL] setting and using variables in PSQL ????

[Gauthier, Dave]

Hi:

 

At the PSQL prompt, I want to set some variables based upon query
results, or via static assignment, then insert a record with those
values.  Sort of like...

 

select val1 into x from agedata where name = 'joe_mako';

select val12 into y from sizedata where name = 'joe_mako';

thename := 'joe_mako';

insert into newtable (name, age, height) values (thename,x,y);

 

Some sort of compound insert statement would solve my problem, but in a
larger sense, how do you (can you ) do something like this inside a
statement blocks of some kind?  I've done some PL-pgsql and know about
statement blocks in that context.  But I don't want to have to create a
function, use it, then delete it.  Was wondering of something else could
be done.

 

Thanks

-dave 

 

Re: [GENERAL] Finding bad bye in "invalid byte sequence" error

[Adrian Klaver]

On Thursday 13 December 2007 10:38 am, robert wrote:
> Hi all,
>
> I'm trying to hack my inserts script from mssql to work with postgres
> 8.1.9 - I can upgrade if need be. I'm getting this error:
>
> psql -h localhost atdev < fuk2.sql
> ERROR:  invalid byte sequence for encoding "UTF8": 0xe1204f
> HINT:  This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".
>
> I tried:
>
> /var/lib/pgsql> recode ascii..utf8 fuk2.sql
> recode: fuk2.sql failed: Invalid input in step `ANSI_X3.4-1968..UTF-8'
>
> And also dos2unix , but nothing is working. 0xe1204f looks like a hex
> address, and I'm trying hexdump to find what its complaining about,
> but that's not helping either. Any ideas?
>
> Robert
>
I had a similiar problem and found I had to set the client encoding as 
follows:

SET client_encoding = 'windows-1252';

at the top of my import file.


-- 
Adrian Klaver
[EMAIL PROTECTED]

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

Re: [GENERAL] mssql migration and boolean to integer problems

[Adrian Klaver]

On Wednesday 12 December 2007 8:09 pm, robert wrote:
> Hi all, I've spent the last few days hacking a mssql INSERT script to
> work with 8.1.9 - I could build the latest postgres source if need be.
> My latest problem is:
>
> ERROR:  column "includeScenario" is of type boolean but expression is
> of type integer
> HINT:  You will need to rewrite or cast the expression.
>
> So mssql uses tiny int for booleans, and I have about 50 of
> those ;-) . I googled alot on this, and tried 4 or 5 different ideas
> with Functions and alter tables -  but I can't find anything that's
> working with 8.1.9, can someone please help me?
>
In 8.2 there is a built in int --> bool cast. I had a similiar problem with 
8.0 and I created my own int::bool cast using the following:

CREATE CAST (int4 AS bool)
  WITH FUNCTION bool(int4)
  AS ASSIGNMENT;

CREATE OR REPLACE FUNCTION bool(int4)
  RETURNS bool AS
$Body$
Declare
output char(1);
Begin
Select into output $1;
Return output;
End;
$Body$
  LANGUAGE 'plpgsql' VOLATILE;

This way I did not have to include the casting in SQL statements. Be aware 
that if you upgrade to 8.2 the restore process will weed out the above 
because of the builtin cast.

-- 
Adrian Klaver
[EMAIL PROTECTED]

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

Re: [GENERAL] Julian Day 0 question

[Tom Lane]

Andrew Chernow <[EMAIL PROTECTED]> writes:
> Looks like a difference in calendars: I think the docs give the starting 
> date in Julian proleptic Calendar while to_char returns Gregorian 
> proleptic Calendar.

Yeah.  We're definitely using Gregorian counting, because we're omitting
leap years at multiples of 400 years:

regression=# select 'J114417'::date;
 date  
---
 4400-02-28 BC
(1 row)

regression=# select 'J114418'::date;
 date  
---
 4400-03-01 BC
(1 row)

So as far as that goes, I'm inclined to leave the code alone and adjust
the docs.

The regular date input routine hasn't got a problem with J0:

regression=# select 'J1'::date;
 date  
---
 4714-11-25 BC
(1 row)

regression=# select 'J0'::date;
 date  
---
 4714-11-24 BC
(1 row)

so to_date's behavior in the case seems a bit broken, but I'm not sure
if it's worth fixing.  IIRC that code uses zero to mean "field not
entered", so it'd need some ugly hack to handle this.

regards, tom lane

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

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

Re: [GENERAL] postgres writer process growing up too much

[Tom Lane]

Richard Huxton <[EMAIL PROTECTED]> writes:
> Heiner Vega wrote:
>> I've been monitoring my postgres processes and I noticed that the resident
>> memory
>> size of the writer process is growing up too much.

> Notice the "SHR"=shared value. That's 155MB virtual memory, 140MB of it 
> resident of which 139MB is shared with other processes. So - nothing to 
> worry about.

The reason the SHR number grows over time is that the system only counts
a page of shared memory against the process after the process has first
touched it.  Once the bgwriter has touched every page of shared buffers,
the number will stop changing.

If there were actually a memory leak, the VIRT number would be growing
... but it's not.

>> My postgres version is 8.1.3

If I were you, I'd be considerably more worried about the fact that
you are running an old release with a pile of known bugs.

regards, tom lane

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

Re: [GENERAL] settings for 8.2.5 on Mac OS X 10.4.11

[Tom Lane]

Matthew Hixson <[EMAIL PROTECTED]> writes:
> What are good settings for shared_buffers and max_connections with  
> the above software combo?  Out of the box I'm getting the "Cannot  
> allocate memory" message upon doing a 'make check'.

The best thing is to fix the OS' ridiculously small shmem limits.
I've got this in /etc/sysctl.conf:

kern.sysv.shmmax=33554432
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=8192

and on a production DB you'd probably want shmmax and shmall
a good bit higher.

Don't forget to reboot after changing settings.  Also, a
"sysctl -a | grep shm" is a good idea to make sure it "took";
OS X is infamously picky about these settings.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Hijack!

[Thomas Hart]

Martijn van Oosterhout wrote:

On Fri, Dec 14, 2007 at 01:55:04PM -0300, Alvaro Herrera wrote:
  
I'm really glad that people don't do that on this list.  I /hate/ getting 
individual email copies from list posters.  I'm going to read it on the 
list; why in the world would I want that clutter in my inbox?
  

Huh, you know you can de-duplicate them at your end, right?  Actually I
prefer to get the private copy, so that I get the email immediately even
if the list server is down or slow.



Or even better, the list server has options like "eliminatecc" and
"rewritefrom" and others that can be set on a per user basis, so you
can configure the list exactly how you like it... No need to complain
to anyone else that it not your preferred way.

Have a nice day,

  

Let's have three cheers for an answer that works for everybody :-)

(can we stop posting on this thread now? pretty please?)

--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)


---(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] Hijack!

[Martijn van Oosterhout]

On Fri, Dec 14, 2007 at 01:55:04PM -0300, Alvaro Herrera wrote:
> > I'm really glad that people don't do that on this list.  I /hate/ getting 
> > individual email copies from list posters.  I'm going to read it on the 
> > list; why in the world would I want that clutter in my inbox?
> 
> Huh, you know you can de-duplicate them at your end, right?  Actually I
> prefer to get the private copy, so that I get the email immediately even
> if the list server is down or slow.

Or even better, the list server has options like "eliminatecc" and
"rewritefrom" and others that can be set on a per user basis, so you
can configure the list exactly how you like it... No need to complain
to anyone else that it not your preferred way.

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] Julian Day 0 question

[Andrew Chernow]

Pavel Stehule wrote:

On 14/12/2007, Andrew Chernow <[EMAIL PROTECTED]> wrote:

Ran across something that is confusing me.  The docs for to_char
indicates that julian day 0 is January 1, 4712 BC at midnight.

http://www.postgresql.org/docs/8.3/static/functions-formatting.html

When I run to_char, I don't get 0 for that date.

postgres=# select to_char('4712-01-01 BC'::date, 'J');
  to_char
-
  404

I get julian day 0 for 4714-11-24 BC.

postgres=# select to_char('4714-11-24 BC'::date, 'J');
  to_char
-
  0

Output of 'select version()'

PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)

andrew



there is more strange things

postgres=# select to_date('0', 'J');
to_date
---
 0001-01-01 BC
(1 row)

it's wrong, correct is probably ERROR:  timestamp out of range

postgres=# select to_date('1', 'J');
to_date
---
 4714-11-25 BC
(1 row)

Regards
Pavel Stehule




Looks like a difference in calendars: I think the docs give the starting 
date in Julian proleptic Calendar while to_char returns Gregorian 
proleptic Calendar.


andrew


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

  http://archives.postgresql.org/

Re: [GENERAL] Julian Day 0 question

[Pavel Stehule]

On 14/12/2007, Andrew Chernow <[EMAIL PROTECTED]> wrote:
> Ran across something that is confusing me.  The docs for to_char
> indicates that julian day 0 is January 1, 4712 BC at midnight.
>
> http://www.postgresql.org/docs/8.3/static/functions-formatting.html
>
> When I run to_char, I don't get 0 for that date.
>
> postgres=# select to_char('4712-01-01 BC'::date, 'J');
>   to_char
> -
>   404
>
> I get julian day 0 for 4714-11-24 BC.
>
> postgres=# select to_char('4714-11-24 BC'::date, 'J');
>   to_char
> -
>   0
>
> Output of 'select version()'
>
> PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
>
> andrew
>

there is more strange things

postgres=# select to_date('0', 'J');
to_date
---
 0001-01-01 BC
(1 row)

it's wrong, correct is probably ERROR:  timestamp out of range

postgres=# select to_date('1', 'J');
to_date
---
 4714-11-25 BC
(1 row)

Regards
Pavel Stehule

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

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

Re: [GENERAL] user name and password woes

[brian]

Richard Huxton wrote:

Charles Mortell wrote:


The dawg that originally set up postgres 8.0 on one of our Windows 2003
servers has left the company, and I have taken it over.
I need to use pg_dump on that server. When I try to, I get ‘user
"Administrator" does not exist.’
   Next I try ‘createuser –U postgres –W administrator –P’.  The 
response is

‘ERROR:  user "administrator" already exists.’



Hmm - is it the capital "A" here? I notice the second attempt has "a".

...

No, the database users are completely separate from OS users. By 
convention, "postgres" is the OS user of the process and superuser for 
the DB, but you could change it if you wanted to.




I've a feeling that Charles is invoking pg_dump without specifying the 
user. The default will assume the current *OS* user. Thus, 
"Administrator" and the "does not exist" error. Try doing:


pg_dump -S administrator ...

(using the postgres-specific administrator user).

brian

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

[GENERAL] settings for 8.2.5 on Mac OS X 10.4.11

[Matthew Hixson]

What are good settings for shared_buffers and max_connections with  
the above software combo?  Out of the box I'm getting the "Cannot  
allocate memory" message upon doing a 'make check'.

  Thanks,
   -M@

---(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] Julian Day 0 question

[Andrew Chernow]

Ran across something that is confusing me.  The docs for to_char 
indicates that julian day 0 is January 1, 4712 BC at midnight.


http://www.postgresql.org/docs/8.3/static/functions-formatting.html

When I run to_char, I don't get 0 for that date.

postgres=# select to_char('4712-01-01 BC'::date, 'J');
 to_char
-
 404

I get julian day 0 for 4714-11-24 BC.

postgres=# select to_char('4714-11-24 BC'::date, 'J');
 to_char
-
 0

Output of 'select version()'

PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)


andrew

---(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] Hijack!

[Andrej Ricnik-Bay]

On 12/15/07, Richard Huxton <[EMAIL PROTECTED]> wrote:
L > Leif B. Kristensen wrote:
O > I  > me too.
L > t  >
  > '  > On Wednesday 12. December 2007, Gregory Stark wrote:
  > s  >> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>>>> Thomas Kellerer wrote:
  > n   Joshua D. Drake, 11.12.2007 17:43:
  > o  > O.k. this might be a bit snooty but frankly it is almost 2008. If
  > t  > you are still a top poster, you obviously don't care about the
  >> people's content that you are replying to, to have enough wits
  >  to
  > t  > not top post.
  > h   I personally find non-trimmed bottom postings at lot more
  >  annoying
  > e   than top-postings. But then that's probably just me.
  >>>> It's not just you.  Much as I am annoyed by top-posting, I am much
  > w  >>> more so by people who top-post at the bottom.  Hey, did I say
  > o  >>> something stupid?  No -- think about it.  These guys do exactly the
  > r  >>> same thing as top-posters, except it is much worse because the
  > s  >>> actual text they wrote is harder to find.
  > t  >>>
  >>>> --

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

   http://archives.postgresql.org/

Re: [GENERAL] size cost for null fields

[Gregory Stark]

"Vance Maverick" <[EMAIL PROTECTED]> writes:

> Do I have this right?  If so, the side table sounds like the right choice

No, if the null bit is set then the field isn't stored at all. You don't pay
the alignment or storage overhead at all.

The only cost is for the null bitmap itself. If you have *any* null fields
then you pay one bit for *every* field plus alignment to a 4-byte boundary.
Except there's one available free byte so if you have 8 or fewer fields even
that is entirely free.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

---(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] mssql migration and boolean to integer problems

[Gregory Stark]

"robert" <[EMAIL PROTECTED]> writes:

> So it has two 'bool' - "includeScenario"  and "deleted" . I have an
> insert like...
>
> INSERT INTO "ASSETSCENARIO"
> ("assetScenarioID",OBJ_VERSION,"includeScenario","scenarioName","probability","occurenceDate","notes","priceTarget","assetID","created","modified","createdUserID","modifiedUserID","deleted")
> VALUES
> (197,0,1,'2007-12-13 11:31:00.000','2007-12-13 11:31:00.000',2,2,NULL);

There's an SQL standard syntax too, but the Postgres-specific syntax is:

postgres=# select 1::bool;
 bool 
--
 t
(1 row)

postgres=# select 0::bool;
 bool 
--
 f
(1 row)


Alternatively you could just quote the inputs. If you insert '0' and '1'
they'll be parsed as boolean values. It's just because you used 0 and 1
without quotes that they're parsed as integers first then don't match the
boolean type.


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

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

   http://archives.postgresql.org/

Re: [GENERAL] postgres writer process growing up too much

[Richard Huxton]

Heiner Vega wrote:

Hi to everyone

I've been monitoring my postgres processes and I noticed that the resident
memory
size of the writer process is growing up too much.



YESTERDAY:

 PID   USER  PR  NI  VIRT  RES  SHR  S %CPU %MEMTIME+  COMMAND



11419 postgres   15   0  155m  112m 112m S  0.0  3.0   0:00.49 postgres:
writer process

TODAY:
 PID   USER  PR  NI  VIRT  RES  SHR  S %CPU %MEMTIME+  COMMAND



11419 postgres   15   0  155m  140m 139m S  0.0  3.7   0:01.09 postgres:
writer process


Notice the "SHR"=shared value. That's 155MB virtual memory, 140MB of it 
resident of which 139MB is shared with other processes. So - nothing to 
worry about.


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

[GENERAL] postgres writer process growing up too much

[Heiner Vega]

Hi to everyone

I've been monitoring my postgres processes and I noticed that the resident
memory
size of the writer process is growing up too much.
Those are reports from top in a 24 hour interval:

YESTERDAY:

 PID   USER  PR  NI  VIRT  RES  SHR  S %CPU %MEMTIME+  COMMAND
11706 postgres   17   0  157m  148m 146m S  0.0  3.9   3:23.41 postgres:
postgres BJ3 127.0.0.1(64648) idle
12174 postgres   16   0  157m  148m 146m S  0.0  3.9   3:17.58 postgres:
postgres BJ3 127.0.0.1(65367) idle
11495 postgres   16   0  157m  148m 146m S  5.7  3.9   7:51.98 postgres:
postgres BJ3 127.0.0.1(64603) idle
11908 postgres   16   0  156m  147m 146m S  5.7  3.9   7:35.84 postgres:
postgres BJ3 127.0.0.1(64947) idle
11419 postgres   15   0  155m  112m 112m S  0.0  3.0   0:00.49 postgres:
writer process

TODAY:
 PID   USER  PR  NI  VIRT  RES  SHR  S %CPU %MEMTIME+  COMMAND
11706 postgres   17   0  157m  148m 146m S  0.0  3.9   7:37.16 postgres:
postgres BJ3 127.0.0.1(64648) idle
12174 postgres   17   0  157m  148m 146m S  0.0  3.9   7:40.10 postgres:
postgres BJ3 127.0.0.1(65367) idle
11495 postgres   16   0  157m  148m 146m S  6.3  3.9  17:46.99 postgres:
postgres BJ3 127.0.0.1(64603) idle
11908 postgres   16   0  156m  147m 146m S  5.7  3.9  17:29.31 postgres:
postgres BJ3 127.0.0.1(64947) idle
11419 postgres   15   0  155m  140m 139m S  0.0  3.7   0:01.09 postgres:
writer process

Any ideas why is this happening?
My postgres version is 8.1.3

Thanks a lot
-- 
Heiner Vega Thames

Re: [GENERAL] user name and password woes

[Richard Huxton]

Charles Mortell wrote:

The dawg that originally set up postgres 8.0 on one of our Windows 2003
servers has left the company, and I have taken it over.
I need to use pg_dump on that server. When I try to, I get ‘user
"Administrator" does not exist.’
   Next I try ‘createuser –U postgres –W administrator –P’.  The response is
‘ERROR:  user "administrator" already exists.’


Hmm - is it the capital "A" here? I notice the second attempt has "a".

Anyway, pgAdmin can display roles/users, and if not "SELECT * FROM 
pg_roles" will do so.



   I know PG requires a user with the same name as the Windows user and that
should be ‘administrator’ on this server.


No, the database users are completely separate from OS users. By 
convention, "postgres" is the OS user of the process and superuser for 
the DB, but you could change it if you wanted to.



   Oddly I can connect to PG on this server through PGAdmin (through
postgres user?).


OK, so you've either got the postgres user password, or none set. That 
should let you do everything you want - dump, restore, create users, 
reset passwords etc.


> Ultimately I want to create a database with a new name from

an existing database; I’ve done this from the command line on other servers.
Can’t, or don’t know how to, do it through PGAdmin.


Well, with SQL you can do CREATE DATABASE  TEMPLATE=
You should be able to run that from PgAdmin, but there's probably a 
dialogue-box that will do it too.



I’ve also tried to delete and recreate the administrator user from 
PGAdmin
but I still can’t use pg_dump.


Use postgres as the user for the moment. Once you've figured out which 
users you have use ALTER USER or ALTER ROLE to reset their passwords. 
See manual for syntax.



   Tried all the passwords my colleagues can think of. I’ve searched the
forums and tried various things like setting pg_hba.conf to trust
temporarily and restarting.
Any suggestions greatly appreciated! Thanks in advance.


Oh, there's a file called ".pgpass" that you can use to hold passwords 
on unix-like systems. I think it's called "pgpass.txt" on Windows but 
check the manuals for details. It will be readable only by the OS user 
that owns it.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] mssql migration and boolean to integer problems

[Richard Broersma Jr]

--- On Thu, 12/13/07, robert <[EMAIL PROTECTED]> wrote:

> > Hi all, I've spent the last few days hacking a
> mssql INSERT script to
> > work with 8.1.9 - I could build the latest postgres
> source if need be.

a standard cast() wouldn't work for you?

proj02u20411=> select cast( 1 as boolean), 
proj02u20411=> cast( 0 as boolean), 
proj02u20411=> cast( -1 as boolean ), 
proj02u20411=> cast( 2 as boolean);

 bool | bool | bool | bool
--+--+--+--
 t| f| t| t

Regards,
Richard Broersma Jr.

---(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] size cost for null fields

[Vance Maverick]

I have a table with lots and lots of rows (into the millions), and I want to 
add some information to it.  The new data consists of a VARCHAR and a BYTEA, 
and it will almost always be null -- let's say only one row in 10,000 will have 
non-null values.  I'm trying to decide whether to add the new data as columns 
in the existing table, or a side table linked by FK.

Looking at the Database Page Layout page 
, it seems 
to me that I'll pay the minimum storage cost for these two fields (4 bytes for 
the VARCHAR, 4 bytes for the BYTEA) in every row, regardless of whether they're 
null.  When the fields are null, there'll be bits set for them in the null 
mask, but the alignment and size of the row won't change -- everything else 
will be placed as if there were zero-length values in the two fields.

Do I have this right?  If so, the side table sounds like the right choice

Vance

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

Re: [GENERAL] query

[Colin Wetherbee]

marco santillan wrote:
I need to be able distribute like my application with postgresql to the 
final client and since as I can assure or encryptar the database to 
avoid the command trust.


Tu pregunta fue traducida mal. Visita por favor el URL siguiente para la 
información sobre la comunidad española de PostgreSQL.


Your question was translated poorly.  Please visit the following URL for 
information on the Spanish PostgreSQL community.


http://archives.postgresql.org/pgsql-es-ayuda/

Colin

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

  http://archives.postgresql.org/

Re: [GENERAL] How can i deal with "\n" when copy tables from sqlserver2005 to postgre?

[Ivan Sergio Borgonovo]

On Fri, 14 Dec 2007 17:29:21 +0800
"bookman bookman" <[EMAIL PROTECTED]> wrote:

> I have solved the "insert NULL" problem,but another problem is
> feazing me now.I have a table t_book in sqlserver,I exported it to

export in csv with DTS and import in pgsql with \copy csv mode. It
should works. I dealt with the same problem mmm 1 month ago.

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


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

Re: [GENERAL] Hijack!

[Richard Huxton]

   Leif B. Kristensen wrote:
I  > me too.
t  >
'  > On Wednesday 12. December 2007, Gregory Stark wrote:
s  >> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
  >>> Thomas Kellerer wrote:
n   Joshua D. Drake, 11.12.2007 17:43:
o  > O.k. this might be a bit snooty but frankly it is almost 2008. If
t  > you are still a top poster, you obviously don't care about the
  > people's content that you are replying to, to have enough wits to
t  > not top post.
h   I personally find non-trimmed bottom postings at lot more annoying
e   than top-postings. But then that's probably just me.
  >>> It's not just you.  Much as I am annoyed by top-posting, I am much
w  >>> more so by people who top-post at the bottom.  Hey, did I say
o  >>> something stupid?  No -- think about it.  These guys do exactly the
r  >>> same thing as top-posters, except it is much worse because the
s  >>> actual text they wrote is harder to find.
t  >>>
  >>> --
w  >>> Alvaro Herrera
a  >>> http://www.flickr.com/photos/alvherre/ "In fact, the basic problem
y  >>> with Perl 5's subroutines is that they're not crufty enough, so the
  >>> cruft leaks out into user-defined code instead, by the Conservation
o  >>> of Cruft Principle."  (Larry Wall, Apocalypse 6)
f  >>>
  >>> ---(end of
r  >>> broadcast)--- TIP 3: Have you checked our
e  >>> extensive FAQ?
p  >>>
l  >>>http://www.postgresql.org/docs/faq
y  >> I agree.
i  >
n  >
g  >

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

Re: [GENERAL] Finding bad bye in "invalid byte sequence" error

[Tom Lane]

robert <[EMAIL PROTECTED]> writes:
> I'm getting this error:

> psql -h localhost atdev < fuk2.sql
> ERROR:  invalid byte sequence for encoding "UTF8": 0xe1204f
> HINT:  This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".

Well, that isn't UTF8, and it isn't plain ASCII either.  I'm betting
it's a single-byte encoding, probably one of the ISO-8859 series.
Have you looked at the data to see what *you* think the character is?
(Feed the script to psql with -f, not <, to get a line number for
the error.)

regards, tom lane

---(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] Finding bad bye in "invalid byte sequence" error

[Douglas McNaught]

On 12/13/07, robert <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm trying to hack my inserts script from mssql to work with postgres
> 8.1.9 - I can upgrade if need be. I'm getting this error:
>
> psql -h localhost atdev < fuk2.sql
> ERROR:  invalid byte sequence for encoding "UTF8": 0xe1204f
> HINT:  This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".

The cleanest way to fix this is to figure out what encoding the data
in your SQL script is (since I am assuming parts of it were dumped
directly out of MSSQL) and set client_encoding to that.  Your data is
almost certainly not straight 7-bit ASCII.

-Doug

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

Re: [GENERAL] Hijack!

[Thomas Hart]

Lew wrote:

Trevor Talbot wrote:

On 12/11/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:


Now, a gripe rightly attributable to the to PG mailing list setup is
that every time I reply, I have to:

(1) use reply all, because reply is set to go to the individual rather
than the list

(2) delete all the individual addressees so only the list is left, then
change that from CC to TO


Actually, another convention on this list is to "reply all" and leave
the individual addresses.


I'm really glad that people don't do that on this list.  I /hate/ 
getting individual email copies from list posters.  I'm going to read 
it on the list; why in the world would I want that clutter in my inbox?


That's why my email address here is a separate one just for Usenet; I 
can pretty much ignore replies that come directly to it.


Actually that's set up that way (I'm purely guessing here) for people 
who subscribe on a "digest" basis, so they can still receive timely 
replies to their issues without having to read every message as it comes 
through.


I think.

Plus, if you'd like (I do), set up a folder for this list, and set up a 
mail rule that forwards anything with [GENERAL] in the name to the 
folder. Keeps it nice and clean (even with a couple other pg lists, and 
a couple apache lists).


--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)


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

Re: [GENERAL] Hijack!

[Alvaro Herrera]

Lew wrote:
> Trevor Talbot wrote:
>> On 12/11/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:
>>
>>> Now, a gripe rightly attributable to the to PG mailing list setup is
>>> that every time I reply, I have to:
>>>
>>> (1) use reply all, because reply is set to go to the individual rather
>>> than the list
>>>
>>> (2) delete all the individual addressees so only the list is left, then
>>> change that from CC to TO
>>
>> Actually, another convention on this list is to "reply all" and leave
>> the individual addresses.
>
> I'm really glad that people don't do that on this list.  I /hate/ getting 
> individual email copies from list posters.  I'm going to read it on the 
> list; why in the world would I want that clutter in my inbox?

Huh, you know you can de-duplicate them at your end, right?  Actually I
prefer to get the private copy, so that I get the email immediately even
if the list server is down or slow.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

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

   http://archives.postgresql.org/

Re: [GENERAL] Hijack!

[Lew]

Gregory Williamson wrote:
* Get a life -- how people post is _trivial_. *content* over *form* ! 
Beating dead horses is of no interest other than the inherent joy in the 
thing. Deal with the fact that an open mail ist will have users from 
*all* backgrounds and origins and it you can't make everything a fight. 
Pick the most important battles. Top-posting is not the worst sin. (not 
reading the manuals is the by the worst transgression, IMHO).


Posting in HTML is kind of a no-no.

And for those who really care, email etiquette in painful detail here 
. Hijacking seems to be more of a 
Bozo No-No than top posting. Or maybe that's just me.



Confidentiality Notice: This e-mail message, including any attachments, 
is for the sole use of the intended recipient(s) and may contain 
confidential and privileged information and must be protected in 
accordance with those provisions. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.


(My corporate masters made me say this.)


Are they aware that your "confidential" messages are on a public board?

Are they aware that routinely and indiscriminately marking all communications 
as "confidential" when some go to a public venue, can reduce or even eliminate 
the protection of confidentiality from such marked communications in certain 
jurisidictions?  IANAL, but as I understand it from /The Hacker Crackdown/ by 
Bruce Sterling, it figured into the defense of a BBS operator accused of 
disseminating "confidential" AT&T information in the U.S. ca. 1990.


--
Lew

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

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

[bookman bookman]

Thank you!


2007/12/12, Obe, Regina <[EMAIL PROTECTED]>:
> Actually what you are doing below is trying to stuff '' in a timestamp
> field.
>
> Keep in mind '' and NULL are not the same.  ''  is invalid for timestamp
> where as NULL is fine.  Your example should be
>  insert into T_Admin(name,key,regDate,isLock,realName)
>   values('aaa','aaa',NULL,'1','aaa');
>
> I think the bcp is trying to insert 'NULL' instead of NULL.
>
> According to the docs - looks like you can tell copy that
> http://www.postgresql.org/docs/techdocs.15
>
> So my guess is you should do
> copy t_admin from "/home/postgres/data/admin.txt" USING DELIMITERS '\t'
> WITH NULL As 'NULL'
>
> Hope that helps,
> Regina
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of bookman bookman
> Sent: Monday, December 10, 2007 9:29 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How can I insert NULL into column with the type of
> timestamp?
>
> H i ,
>
> I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I
> use bcp to export a table named admin in sqlserver to a text file:
>
> --table T_admin
> id name   key  regDate
> isLock  realName
> 1   rison 9988772007-08-27 10:24:57 False
> admin
> 2   lijun 778899NULL
> False NULL
> 3   guanliyuan112007-11-05 10:30:08 False
> myAdmin
>
>   --admin.txt
> id   name  key   regDate
>  isLockrealname
> 1   ris 998877  2007-08-27 10:24:57.000 0   admin
> 2   lij 778899  0
> 3   guanliyuan  11  2007-11-05 10:30:08.813 0
> myAdmin
>
> I created a table in postgresql,and I use "copy" to import datas
>
>  create table T_Admin(
>adminID serial not null primary key,
>name varchar(30) null,
>key varchar(30) null,
>regDate timestamp null,
>isLock bool null,
>realName varchar(30) null
> )
>
>  copy admin from "/home/postgres/data/admin.txt"
>
> Then error occured:
>error:invalid input syntax for type timestamp:""
>context:copy T_Admin ,line 2,column regDate:""
> It seemed that the column REGDATE cannot accept a NULL.I tested it use:
>   insert into T_Admin(name,key,regDate,isLock,realName)
>   values('aaa','aaa','','1','aaa');
> The same error occured.
>
>  So it means that the column with type timestamp cannot accept a NULL
> ..Is there any way I can tansfer this table into postgre?How can i deal
> with NULL in this case?
>   Thank you!
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>   http://www.postgresql.org/docs/faq
> -
> 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] Hijack!

[Lew]

Gregory Williamson wrote:

Well, off to top post on some other forums ... ;-)


statman wrote:
 Should that not be "Well, off to post on some other 
fora"?  8¬>


No.  It /can/ be, but it /needn't/ be.  Actually, saying "fora" is variously 
considered affected, pompous or silly, and is done either out of excessive 
pedantry or humorous rhetorical style.


As in this instance.


The English plural forums is preferred to the Latin plural fora in normal 
English usage.
 * Ref: Modern English Usage, 2nd Edition, ed. Sir Ernest Gowers, Oxford 1968 
(article '-um', p.658).

From 

--
Lew
You want picky?  I got picky!

---(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] Anomalia file FILBD.TXT

[Vincenzo Romano]

Buon giorno.
Nel file FILBD.TXT sono presenti dei prodotti per il quali il campo
"Unita' di Misura" (offset 182) presenza il valore "PM".
Allego la lista dei prodotti interessati.

-- 
Vincenzo Romano
NotOrAnd.IT
Information Technologies
cel. +39 339 8083886
tel. +39 0823 454163
fax. +39 02 700506964

003060023,PM
021909092,PM
022483010,PM
022483022,PM
022483034,PM
022593127,PM
022593139,PM
023383033,PM
023594017,PM
024294011,PM
024475030,PM
025118035,PM
025278060,PM
025430063,PM
025430101,PM
025513033,PM
025587015,PM
028366019,PM
028366021,PM
028366033,PM
028678035,PM
028775017,PM
028775029,PM
029177021,PM
029177084,PM
030106013,PM
032162012,PM
032799013,PM
032799025,PM
033190012,PM
033464025,PM
033529013,PM
033586013,PM
033616020,PM
033652013,PM
034273019,PM
034425013,PM
034425025,PM
034425037,PM
034425049,PM
034452110,PM
034619015,PM
034619027,PM
034799015,PM
035148016,PM
035408044,PM
035906039,PM
036753061,PM
036753248,PM
036757021,PM
036778064,PM
036861060,PM
036861247,PM
037219060,PM
037219247,PM
037219452,PM
037322017,PM
037322029,PM
037322031,PM
037322043,PM
037322056,PM
037322068,PM
037322070,PM
037322082,PM
037322094,PM
037322106,PM
037322118,PM
037322120,PM
037322132,PM
037322144,PM
037322157,PM
037322169,PM
037322171,PM
037322183,PM
037322195,PM
037322207,PM
037322219,PM
03731,PM
037322233,PM
037322245,PM
037322258,PM
037322272,PM
037558018,PM
037558020,PM
037558032,PM
037558044,PM
037558057,PM
037558069,PM
037963016,PM

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

Re: [GENERAL] timestamp with time zone

[Patrick TJ McPhee]

In article <[EMAIL PROTECTED]>,
Tatsuo Ishii <[EMAIL PROTECTED]> wrote:
% From: "Magnus Hagander" <[EMAIL PROTECTED]>

[...]

% > > Can I get "+04" without knowing that I inserted the data using "+0400"
% > > time zone?

% > No. The closest you can get is to store the tz in a different column
% and use AT TIMEZONE (which accepts a column name as argument)

% Or use date + time with time zone.

This could cause problems if you want to insert a timestamp for a date
with different DST settings. It also seems a lot more cumbersome to do
it this way.

-- 

Patrick TJ McPhee
North York  Canada
[EMAIL PROTECTED]

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

   http://archives.postgresql.org/

Re: [GENERAL] Hijack!

[Lew]

Keith Turner wrote:

Thank you for your response. What may be obvious to some isn't always to
others.  It's never a bad idea to remind users how you want your data
formatted if there are roadblocks that are not obvious on the surface. 


Most newsreaders, not just Thunderbird, use the posts' headers to support the 
"threading" feature - whether or not that's obvious to one as a new user 
notwithstanding.  It is what it is.


It won't be obvious to the newcomer what the problem is until someone lets 
them know.  It would be unfortunate if that newcomer were to take such 
education as a "scolding" and take offense.


--
Lew

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

  http://archives.postgresql.org/

Re: [GENERAL] Planner ignoring to use INDEX SCAN

[Ashish Karalkar]

Richard Huxton <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote:
> query which was taking seconds on the join of these two table
> suddenly started taking 20/25 min

Show the EXPLAIN ANALYSE of your problem query and someone will be able 
to tell you why.

Here is the output from explain analyse:
table structures are more or less same with delivery being parent and sms_new 
being child having index on deliveryid in both tables.

 HashAggregate  (cost=6153350.21..6153352.38 rows=174 width=32)
   ->  Hash Join  (cost=218058.30..6153259.97 rows=6016 width=32)
 Hash Cond: ("outer".deliveryid = "inner".deliveryid)
 ->  Seq Scan on sms_new  (cost=0.00..5240444.80 rows=138939341 width=8)
   Filter: ((otid)::text !~~ 'ERROR%'::text)
 ->  Hash  (cost=218057.87..218057.87 rows=174 width=32)
   ->  Bitmap Heap Scan on delivery  (cost=2218.02..218057.87 
rows=174 width=32)
 Recheck Cond: ((createddate >= '2007-12-10 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
00:00:00'::timestamp without time zone))
 Filter: ((taskid = 14267) AND (((remoteip)::text = 
'192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR 
((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = 
'192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR 
((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = 
'202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR 
((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = 
'192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR 
((remoteip)::text = '202.162.231.7'::text)))
 ->  Bitmap Index Scan on createddate_idx  
(cost=0.00..2218.02 rows=207004 width=0)
   Index Cond: ((createddate >= '2007-12-10 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
00:00:00'::timestamp without time zone))


   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


   
-
 Get the freedom to save as many mails as you wish. Click here to know how.

Re: [GENERAL] convert function

[Jan Sunavec]

Thanks a lot

Lots like nice a easy solution.. I am not sure if this is fast solution..  
Many convertions you know.. :-(

Thanks a lot anyway.

  John

On Wed, 12 Dec 2007 17:13:01 +0100, Pavel Stehule  
<[EMAIL PROTECTED]> wrote:



Hello

It's look like SQL_ASCII support diacritic chars now. First you have
to encode from bytea to text

postgres=# SELECT encode(convert('ján', 'UNICODE',  
'SQL_ASCII'),'escape');

 encode

 ján
(1 row)

you wont
postgres=# SELECT to_ascii(encode(convert_to('ján',
'latin2'),'escape'),'latin2');
 to_ascii
--
 jan
(1 row)

Regards
Pavel Stehule



convert do conversion from text to bytea type. For diacritic
elimination use to_ascii function:

postgres=# select to_ascii(convert('Příliš žlutý kůň' using
utf8_to_iso_8859_2),'latin2');
 to_ascii
--
 Prilis zluty kun
(1 row)


On 12/12/2007, Jan Sunavec <[EMAIL PROTECTED]> wrote:

Hi all

I have problem with "convert" function. Previous behaviour was
SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
===
jan

In postgresql 8.3 is quite new behaviour.
SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
==
"j\241n"

This, drives me crazy. I mean, this is not useable for non english
country. I don't need convert to \241 characters. I understand that
someone need this behavour. But there should be possibility switch to
"normal" behaviour.

   John

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

[GENERAL] Finding bad bye in "invalid byte sequence" error

[robert]

Hi all,

I'm trying to hack my inserts script from mssql to work with postgres
8.1.9 - I can upgrade if need be. I'm getting this error:

psql -h localhost atdev < fuk2.sql
ERROR:  invalid byte sequence for encoding "UTF8": 0xe1204f
HINT:  This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".

I tried:

/var/lib/pgsql> recode ascii..utf8 fuk2.sql
recode: fuk2.sql failed: Invalid input in step `ANSI_X3.4-1968..UTF-8'

And also dos2unix , but nothing is working. 0xe1204f looks like a hex
address, and I'm trying hexdump to find what its complaining about,
but that's not helping either. Any ideas?

Robert

---(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] pgsql constraints and temporal tables

[Enrico Sirola]

Hi Tom!

Tom Lane ha scritto:
> Enrico Sirola <[EMAIL PROTECTED]> writes:
>> However, I'm facing a problem I'm not able to solve with postgresql:
>> usually updating or deleting time-varying data means to temporary
>> violate a constraint in a transaction but ensuring that at the end of
>> the transaction the table(s) is(are) in a consistent state, but this
>> seems impossible to do because trigger constraints are not deferrable.

> Use CREATE CONSTRAINT TRIGGER:
> http://developer.postgresql.org/pgdocs/postgres/sql-createconstraint.html
> 
> (This statement has been there all along, but was marked as deprecated
> in pre-8.3 releases, for now-forgotten reasons.)
> 
>   regards, tom lane

ah! My fault! I created the triggers with "create constraint trigger",
but was misleaded by the documentation on constraints, that says
triggers are not deferrables.

Thanks for the help,
e.

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

[GENERAL] user name and password woes

[Charles Mortell]

The dawg that originally set up postgres 8.0 on one of our Windows 2003
servers has left the company, and I have taken it over.
I need to use pg_dump on that server. When I try to, I get ‘user
"Administrator" does not exist.’
   Next I try ‘createuser –U postgres –W administrator –P’.  The response is
‘ERROR:  user "administrator" already exists.’
   I know PG requires a user with the same name as the Windows user and that
should be ‘administrator’ on this server.
   Oddly I can connect to PG on this server through PGAdmin (through
postgres user?). Ultimately I want to create a database with a new name from
an existing database; I’ve done this from the command line on other servers.
Can’t, or don’t know how to, do it through PGAdmin.
I’ve also tried to delete and recreate the administrator user from 
PGAdmin
but I still can’t use pg_dump.
   Tried all the passwords my colleagues can think of. I’ve searched the
forums and tried various things like setting pg_hba.conf to trust
temporarily and restarting.
Any suggestions greatly appreciated! Thanks in advance.

Charles Mortell
Advanced Planning Technologies, Inc.
Mississippi River port of La Crosse, WI
cmortell at apt-cafm dot com


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

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

Re: [GENERAL] Trigger - will not perform INSERT

[smiley2211]

Thanks all...ended up being a missing field in my table definition...sorry to
bother you all ...

:wistle:
-- 
View this message in context: 
http://www.nabble.com/Trigger---will-not-perform-INSERT-tp14282848p14297220.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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

Re: [GENERAL] Planner ignoring to use INDEX SCAN

[Gregory Stark]

"Ashish Karalkar" <[EMAIL PROTECTED]> writes:

> Thanks Richard for your replay,
>
> Richard Huxton <[EMAIL PROTECTED]> wrote: 
>> 
> Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It 
> doesn't show what actually happened, just what the planner thought was 
> going to happen.
>
> Its from EXPLAIN ANALYSE

No, it's not. Not unless you've removed all the added info explain analyse
adds to the explain output.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

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

[GENERAL] query

[marco santillan]

Hello 
I need to be able distribute like my application with postgresql to the final 
client and since as I can assure or encryptar the database to avoid the command 
trust.

Thank you




   
__ 
¿Chef por primera vez?
Sé un mejor Cocinillas. 
http://es.answers.yahoo.com/info/welcome

Re: [GENERAL] what is the date format in binary query results

[Andrew Chernow]

Merlin Moncure wrote:

On Dec 12, 2007 2:14 AM, Samantha Atkins <[EMAIL PROTECTED]> wrote:

This brings up a second question.  How should I do byte order
conversion for 8 byte ints?  I can't use hton ntoh routines as they
max out at 32 bits.  Is there a better way?  Also, are floating point
numbers guaranteed uniform?

If any one knows a a clean code example of binary binding of
parameters and binary extraction of results for all major types in C
against lippq that would be a very useful thing to have in the
standard docs.


We are working on a patch (not necessarily to be migrated with the
source code) to allow simplified binding of binary types to native C
types.  You can see an older version here: it has examples how to read
off a lot of the types in binary.  We should have a new patch in a day
or two that should demonstrate how to read the rest of the types in
binary.  We also handled 64 bit ints...

the patch is here
http://archives.postgresql.org/pgsql-patches/2007-12/msg00014.php

if you are patient we can provide examples for all the basic built in
types, including possibly arrays...

merlin

p.s  don't top post, but I dig Ron Paul :-)




This is from the patch merlin mentioned.

static unsigned int *swap8(void *outp, void *inp, int tonet)
{
  static int n = 1;
  unsigned int *in = (unsigned int *)inp;
  unsigned int *out = (unsigned int *)outp;

  /* swap when needed */
  if(*(char *)&n == 1)
  {
out[0] = (unsigned int)(tonet ? htonl(in[1]) : ntohl(in[1]));
out[1] = (unsigned int)(tonet ? htonl(in[0]) : ntohl(in[0]));
  }
  else
  {
out[0] = in[0];
out[1] = in[1];
  }

  return out;
}

// example
if(PQfformat(res, field_num) == 1)
{
  long long n;
  swap8(&n, PQgetvalue(res, 0, 0), 0);
  printf("%lld\n", n);
}

andrew










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

  http://archives.postgresql.org/

[GENERAL] How can i deal with "\n" when copy tables from sqlserver2005 to postgre?

[bookman bookman]

H i ,

I have solved the "insert NULL" problem,but another problem is feazing
me now.I have a table t_book in sqlserver,I exported it to t_book.txt
,the column content has some special show( I made it purposely).

--t_book.txt
 bookid(int)bookname(varchar(50))
 content
1JavaScript: The Definitive Guide
This

 is  avery

 good  book!

 I wish you will enjoy it!
2 performancePgsql


I created the same table in postgre:

  CREATE TABLE t_book(
 bookid serial not null primary key,
 bookname varchar(50) not null,
 content text null
)

Then I copied it to t_book in postgre,

--command
copy t_book from '/home/postgres/data/t_book.txt' WITH NULL AS ''

and came across the error:invailid input syntax for integer:"is"

I inserted the table t_book in postgre in command line client and
coped it to a file t_book1.txt:

 --command
  copy t_book from '/home/postgres/data/t_book1.txt' WITH NULL AS ''

This is t_book1.txt

  --t_book1.txt
1JavaScript: The Definitive Guide  This\n is
   avery\good  book!\n I wish you will enjoy it!
2performancePgsql

I also used CSV mode and the the file changed:

 --t_book2.txt
1JavaScript: The Definitive Guide
   " This

 is  avery

 good  book!

 I wish you will enjoy it!"
2 performancePgsql

So I found when i copied t_book.txt into table t_book ,postgresql
treated "isavery   goodbook!"  as a new line and
inserted "is" into bookid.

This means the content of a table column  must be include in a pair of
quote if it not in a line .The problem is I cannot export the table
with some columns in quote(like content column) and the others
not(like bookid,bookname).If I export a table with quote,all of its
columns will be include in quote,like this:

--t_book5.txt
"1"   " JavaScript: The Definitive
Guide "" This

 is  avery

 good  book!

 I wish you will enjoy it!"
"2"" performancePgsql "
 ""

If I copy it into t_book in postgre,the error must be:
  invalid input syntax for integer:"1"
because the type of "1" is varchar but integer.

So how can i copy tables in sqlserver to postgre when content of a
column not in a line?
Can I copy the file of t_book5.txt into postgre?

  Thank you very much!

---(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] top posting

[Lew]

Andrew Sullivan wrote:
We run this list in English, note.  Is that because it's better than Latin? 
No: it's because more of the participants like it that way.  I bet if we had

a lot of Latin speakers, we'd have made a different decision.  And yes,
there's a certain amount of circularity in such convention-picking (because
by choosing English, we surely discriminate against the unilingual Latin
speakers).


De mortuis nil nisi bonum dicendum est.

--
Lew

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

Re: [GENERAL] Hijack!

[Lew]

Trevor Talbot wrote:

On 12/11/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:


Now, a gripe rightly attributable to the to PG mailing list setup is
that every time I reply, I have to:

(1) use reply all, because reply is set to go to the individual rather
than the list

(2) delete all the individual addressees so only the list is left, then
change that from CC to TO


Actually, another convention on this list is to "reply all" and leave
the individual addresses.


I'm really glad that people don't do that on this list.  I /hate/ getting 
individual email copies from list posters.  I'm going to read it on the list; 
why in the world would I want that clutter in my inbox?


That's why my email address here is a separate one just for Usenet; I can 
pretty much ignore replies that come directly to it.


--
Lew

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

Re: [GENERAL] top posting

[Lew]

Ron St-Pierre wrote:
I agree that top-posting can sometimes be easier to read. However, from 
the perspective of someone who *often* searches the archives for answers 
it is usually *much* easier to find a complete problem/solution set when 
the responses are bottom posted and/or interleaved.


The objection people have about a short answer after a long post isn't cured 
by top-posting.  It's cured by editing the quoted material to provide 
appropriate context without letting the length go bad.


Jo: It made the post harder to read.
Mo: Why is it bad?
Jo: Top-posting.
Mo: What was the problem with my post?

--
Lew

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

Re: [GENERAL] mssql migration and boolean to integer problems

[robert]

On Dec 12, 11:09 pm, robert <[EMAIL PROTECTED]> wrote:
> Hi all, I've spent the last few days hacking a mssql INSERT script to
> work with 8.1.9 - I could build the latest postgres source if need be.
> My latest problem is:
>
> ERROR:  column "includeScenario" is of type boolean but expression is
> of type integer
> HINT:  You will need to rewrite or cast the expression.
>
> So mssql uses tiny int for booleans, and I have about 50 of
> those ;-) . I googled alot on this, and tried 4 or 5 different ideas
> with Functions and alter tables -  but I can't find anything that's
> working with 8.1.9, can someone please help me?
>
> Thanks,
> Robert

Really stuck, please help. I have this table:

create table "ASSETSCENARIO" ("assetScenarioID" int8 not null,
OBJ_VERSION int8 not null, "includeScenario" bool, "scenarioName"
varchar(2000), "probability" int8, "occurenceDate" timestamp, "notes"
varchar(2000), "priceTarget" float8, "assetID" int8 not null,
"created" timestamp not null, "modified" timestamp not null,
"createdUserID" int8 not null, "modifiedUserID" int8 not null,
"deleted" bool, primary key ("assetScenarioID"));

So it has two 'bool' - "includeScenario"  and "deleted" . I have an
insert like...

INSERT INTO
"ASSETSCENARIO" 
("assetScenarioID",OBJ_VERSION,"includeScenario","scenarioName","probability","occurenceDate","notes","priceTarget","assetID","created","modified","createdUserID","modifiedUserID","deleted")VALUES(197,0,1,'2007-12-13
11:31:00.000','2007-12-13 11:31:00.000',2,2,NULL);

I've tried:

CREATE FUNCTION boolean_integer_compare(boolean,integer) RETURNS
boolean AS $$
SELECT ($2 = 1 AND $1) OR ($2 = 0 AND NOT $1);
$$ LANGUAGE SQL;

CREATE OPERATOR = (
leftarg = boolean,
rightarg = integer,
procedure = boolean_integer_compare,
commutator = =
);

And alternatively:

CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean
AS '
begin
return not inttobool($1,$2);
end;
'
LANGUAGE plpgsql;

CREATE OPERATOR = (
PROCEDURE = inttobool,
LEFTARG = boolean,
RIGHTARG = integer,
COMMUTATOR = =,
NEGATOR = <>
);

CREATE OPERATOR <> (
PROCEDURE = notinttobool,
LEFTARG = integer,
RIGHTARG = boolean,
COMMUTATOR = <>,
NEGATOR = =
);

CREATE OPERATOR = (
PROCEDURE = inttobool,
LEFTARG = integer,
RIGHTARG = boolean,
COMMUTATOR = =,
NEGATOR = <>
);

CREATE OPERATOR <> (
PROCEDURE = notinttobool,
LEFTARG = boolean,
RIGHTARG = integer,
COMMUTATOR = <>,
NEGATOR = =
);

Lastly, I tried:

ALTER TABLE table
ALTER COLUMN field1 TYPE boolean
USING CASE WHEN field1 = 0 THEN FALSE
WHEN field1 = 1 THEN TRUE
ELSE NULL
END;

Each time I get:

ERROR:  column "includeScenario" is of type boolean but expression is
of type integer
HINT:  You will need to rewrite or cast the expression.

Right now I'm trying to "cast the expression." - how do I do that in
this case?

Thanks,
Robert

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

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

[GENERAL] mssql migration and boolean to integer problems

[robert]

Hi all, I've spent the last few days hacking a mssql INSERT script to
work with 8.1.9 - I could build the latest postgres source if need be.
My latest problem is:

ERROR:  column "includeScenario" is of type boolean but expression is
of type integer
HINT:  You will need to rewrite or cast the expression.

So mssql uses tiny int for booleans, and I have about 50 of
those ;-) . I googled alot on this, and tried 4 or 5 different ideas
with Functions and alter tables -  but I can't find anything that's
working with 8.1.9, can someone please help me?

Thanks,
Robert




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

Re: [GENERAL] pgsql constraints and temporal tables

[Tom Lane]

Enrico Sirola <[EMAIL PROTECTED]> writes:
> However, I'm facing a problem I'm not able to solve with postgresql:
> usually updating or deleting time-varying data means to temporary
> violate a constraint in a transaction but ensuring that at the end of
> the transaction the table(s) is(are) in a consistent state, but this
> seems impossible to do because trigger constraints are not deferrable.

Use CREATE CONSTRAINT TRIGGER:
http://developer.postgresql.org/pgdocs/postgres/sql-createconstraint.html

(This statement has been there all along, but was marked as deprecated
in pre-8.3 releases, for now-forgotten reasons.)

regards, tom lane

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

Re: [GENERAL] rewrite pl/pgsql functions to c - remote job

[Stephen Frost]

* Marek Lewczuk ([EMAIL PROTECTED]) wrote:
> I'm looking for a C developer that is able to rewrite pl/pgsql functions to 
> PostgreSQL c functions - because we need better performance we would like 
> to have all important functions rewritten to c (looking from pl/pgsql point 
> of view they are not complicated, so I thing that an C export should do it 
> in a moment). If any of you have time and want to earn some money, please 
> write me an email.

As mentioned in another post- you may or may not get the performance
increase you're looking for by moving the functions to C.  Can you post
the pl/pgsql functions somewhere and/or describe what in them is being
slow?  It'd go a long way towards figuring out if C would actually help
all that much or not.

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] rewrite pl/pgsql functions to c - remote job

[Merlin Moncure]

On Dec 14, 2007 7:50 AM, Marek Lewczuk <[EMAIL PROTECTED]> wrote:
> Hello all,
> I'm looking for a C developer that is able to rewrite pl/pgsql functions
> to PostgreSQL c functions - because we need better performance we would
> like to have all important functions rewritten to c (looking from
> pl/pgsql point of view they are not complicated, so I thing that an C
> export should do it in a moment). If any of you have time and want to
> earn some money, please write me an email.

Out of curiosity, what are you trying to speed up by moving to C?
some things _are_ faster in C procedures, but usually not in the
manner you would expect.  The big reason to write C procedures IMO is
to do things that are normally not possible in other languages.

merlin

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

Re: [GENERAL] rewrite pl/pgsql functions to c - remote job

[Richard Huxton]

Marek Lewczuk wrote:

Hello all,
I'm looking for a C developer that is able to rewrite pl/pgsql functions 
to PostgreSQL c functions


You might want to post this to the -jobs mailing list instead.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Planner ignoring to use INDEX SCAN

[Richard Huxton]

Ashish Karalkar wrote:

Thanks Richard for your replay,

here is the output..

Richard Huxton <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote:

Richard Huxton  wrote: Ashish Karalkar wrote:

query which was taking seconds on the join of these two table
suddenly started taking 20/25 min
Show the EXPLAIN ANALYSE of your problem query and someone will be able 
to tell you why.


Here is the output from explain analyse:


Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It 
doesn't show what actually happened, just what the planner thought was 
going to happen.


Its from EXPLAIN ANALYSE


No it's not, because that shows an extra set of figures. If you see 
below there is a cost for each stage and an actual time too.


EXPLAIN ANALYSE SELECT d.id, l.name FROM items.documents d JOIN 
lookups.document_class l ON d.class=l.id;

  QUERY PLAN
---
 Hash Join  (cost=1.16..14.00 rows=162 width=19) (actual 
time=0.100..0.392 rows=162 loops=1)

   Hash Cond: ((d.class)::text = (l.id)::text)
   ->  Seq Scan on documents d  (cost=0.00..10.62 rows=162 width=9) 
(actual time=0.024..0.121 rows=162 loops=1)
   ->  Hash  (cost=1.07..1.07 rows=7 width=20) (actual 
time=0.029..0.029 rows=8 loops=1)
 ->  Seq Scan on document_class l  (cost=0.00..1.07 rows=7 
width=20) (actual time=0.008..0.015 rows=8 loops=1)

 Total runtime: 0.506 ms
(6 rows)


Are the row-estimates roughly accurate?

Yes Row count of sms_new is approx. same


OK, that's good.


table structures are more or less same with delivery being parent and sms_new 
being child having index on deliveryid in both tables.

 HashAggregate  (cost=6153350.21..6153352.38 rows=174 width=32)
   ->  Hash Join  (cost=218058.30..6153259.97 rows=6016 width=32)
 Hash Cond: ("outer".deliveryid = "inner".deliveryid)
 ->  Seq Scan on sms_new  (cost=0.00..5240444.80 rows=138939341 width=8)
   Filter: ((otid)::text !~~ 'ERROR%'::text)
 ->  Hash  (cost=218057.87..218057.87 rows=174 width=32)


Well, it knows that it's going to be expensive (cost=5240444.80). Since 
it thinks you'll only get 174 rows from the other side and 6016 
matching, I can't see how an index could be calculated as more expensive.


Try issuing ENABLE seq_scan=off and re-running the EXPLAIN, let's see 
what cost that comes up with.


here is explain out put after setting enable_seqscan=off

 HashAggregate  (cost=27729224.21..27729226.21 rows=160 width=32)
   ->  Nested Loop  (cost=2534.67..27729143.31 rows=5393 width=32)
 ->  Bitmap Heap Scan on delivery  (cost=2094.41..216143.78 rows=160 
width=32)



 ->  Bitmap Heap Scan on sms_new  (cost=440.26..171369.61 rows=46931 
width=8)


Well, the estimated cost for this one is up to 27 million from the 
previous 6 million. It's doing two bitmap scans and then the nested loop 
which is what's pushing the cost up.


Can you post the query too?

--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

[GENERAL] rewrite pl/pgsql functions to c - remote job

[Marek Lewczuk]

Hello all,
I'm looking for a C developer that is able to rewrite pl/pgsql functions 
to PostgreSQL c functions - because we need better performance we would 
like to have all important functions rewritten to c (looking from 
pl/pgsql point of view they are not complicated, so I thing that an C 
export should do it in a moment). If any of you have time and want to 
earn some money, please write me an email.


Best regards,
ML



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

  http://archives.postgresql.org/

Re: [GENERAL] Planner ignoring to use INDEX SCAN

[Ashish Karalkar]

Thanks Richard for your replay,

here is the output..

Richard Huxton <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote:
> 
> Richard Huxton  wrote: Ashish Karalkar wrote:
>> query which was taking seconds on the join of these two table
>> suddenly started taking 20/25 min
> 
> Show the EXPLAIN ANALYSE of your problem query and someone will be able 
> to tell you why.
> 
> Here is the output from explain analyse:

Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It 
doesn't show what actually happened, just what the planner thought was 
going to happen.

Its from EXPLAIN ANALYSE


Are the row-estimates roughly accurate?

Yes Row count of sms_new is approx. same

> table structures are more or less same with delivery being parent and sms_new 
> being child having index on deliveryid in both tables.
> 
>  HashAggregate  (cost=6153350.21..6153352.38 rows=174 width=32)
>->  Hash Join  (cost=218058.30..6153259.97 rows=6016 width=32)
>  Hash Cond: ("outer".deliveryid = "inner".deliveryid)
>  ->  Seq Scan on sms_new  (cost=0.00..5240444.80 rows=138939341 
> width=8)
>Filter: ((otid)::text !~~ 'ERROR%'::text)
>  ->  Hash  (cost=218057.87..218057.87 rows=174 width=32)

Well, it knows that it's going to be expensive (cost=5240444.80). Since 
it thinks you'll only get 174 rows from the other side and 6016 
matching, I can't see how an index could be calculated as more expensive.

Try issuing ENABLE seq_scan=off and re-running the EXPLAIN, let's see 
what cost that comes up with.

here is explain out put after setting enable_seqscan=off

 HashAggregate  (cost=27729224.21..27729226.21 rows=160 width=32)
   ->  Nested Loop  (cost=2534.67..27729143.31 rows=5393 width=32)
 ->  Bitmap Heap Scan on delivery  (cost=2094.41..216143.78 rows=160 
width=32)
   Recheck Cond: ((createddate >= '2007-12-10 00:00:00'::timestamp 
without time zone) AND (createddate <= '2007-12-11 00:00:00'::timestamp without 
time zone))
   Filter: ((taskid = 14267) AND (((remoteip)::text = 
'192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR 
((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = 
'192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR 
((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = 
'202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR 
((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = 
'192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR 
((remoteip)::text = '202.162.231.7'::text)))
   ->  Bitmap Index Scan on createddate_idx  (cost=0.00..2094.41 
rows=197068 width=0)
 Index Cond: ((createddate >= '2007-12-10 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
00:00:00'::timestamp without time zone))
 ->  Bitmap Heap Scan on sms_new  (cost=440.26..171369.61 rows=46931 
width=8)
   Recheck Cond: (sms_new.deliveryid = "outer".deliveryid)
   Filter: ((otid)::text !~~ 'ERROR%'::text)
   ->  Bitmap Index Scan on sms_new_deliveryid_idx  
(cost=0.00..440.26 rows=46931 width=0)
 Index Cond: (sms_new.deliveryid = "outer".deliveryid)




Oh, and I take it sms_new is recently vacuumed and analysed?

yes it is vacuumed and analysed



-- 
   Richard Huxton
   Archonet Ltd


   
-
 Share files, take polls, and discuss your passions - all under one roof.  
Click here.

Re: [GENERAL] Planner ignoring to use INDEX SCAN

[Richard Huxton]

Ashish Karalkar wrote:


Richard Huxton <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote:

query which was taking seconds on the join of these two table
suddenly started taking 20/25 min


Show the EXPLAIN ANALYSE of your problem query and someone will be able 
to tell you why.


Here is the output from explain analyse:


Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It 
doesn't show what actually happened, just what the planner thought was 
going to happen.


Are the row-estimates roughly accurate?


table structures are more or less same with delivery being parent and sms_new 
being child having index on deliveryid in both tables.

 HashAggregate  (cost=6153350.21..6153352.38 rows=174 width=32)
   ->  Hash Join  (cost=218058.30..6153259.97 rows=6016 width=32)
 Hash Cond: ("outer".deliveryid = "inner".deliveryid)
 ->  Seq Scan on sms_new  (cost=0.00..5240444.80 rows=138939341 width=8)
   Filter: ((otid)::text !~~ 'ERROR%'::text)
 ->  Hash  (cost=218057.87..218057.87 rows=174 width=32)


Well, it knows that it's going to be expensive (cost=5240444.80). Since 
it thinks you'll only get 174 rows from the other side and 6016 
matching, I can't see how an index could be calculated as more expensive.


Try issuing ENABLE seq_scan=off and re-running the EXPLAIN, let's see 
what cost that comes up with.


Oh, and I take it sms_new is recently vacuumed and analysed?

--
  Richard Huxton
  Archonet Ltd

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

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

[GENERAL] pgsql constraints and temporal tables

[Enrico Sirola]

Hello,
I'm reading "Developing Time-Oriented Database Applications in SQL" by
Richard Snodgrass, and trying to reimplement some of the examples using
postgresql. The book is about temporal tables and applications involving
time-varying data; the one of the main difficulties with such problems
is that SQL does not support "temporal" referential integrity, so you
have to code it with triggers or checks (well, in the pgsql case
triggers, because check constraints doesn't support subqueries which are
useful in these cases).

However, I'm facing a problem I'm not able to solve with postgresql:
usually updating or deleting time-varying data means to temporary
violate a constraint in a transaction but ensuring that at the end of
the transaction the table(s) is(are) in a consistent state, but this
seems impossible to do because trigger constraints are not deferrable.
So here is the question: how do you use trigger constraints to ensure
complex referential integrity and at the same time are still able to
update the table data? I'm sure this question is maybe a bit too
generic, I hope someone here in the list readed the book (which is very
interesting and electronic copies are available for free) and is willing
to help. If interested, I can post an example here...
Thanks,
Enrico

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

Re: [GENERAL] Hash join in 8.3

[André Volpato]

Gregory Stark escreveu:

André Volpato <[EMAIL PROTECTED]> writes:
  

I think I found the answer!

8.1: likes nested loop even after vacuumdb on the database.

8.3: likes hash at first time but:
- after vacuumdb *on the database* (I was running on the tables.), it turns 
out to:
Merge Join (cost=178779.93..328503.44 rows=3 width=38) in 20005.207 ms
# set enable_mergejoin=off;
Hash Join(cost=156644.00..365204.03 rows=3 width=38) in 29104.390 ms
* a very faster hash here, seqscanning the smaller table before the bigger one. 
Tricky!

I wont trust table vacuums anymore...




HTML-only mail isn't looked upon too favourably here.
  
My bad. Tbird for some reason isn´t auto-removing html in sent mail to 
@postgresql.org.



You keep saying "vacuum" which makes me think maybe you're not actually
analyzing your tables at all. "vacuum" doesn't analyze the tables, you have to
run "analyze" (or "vacuum analyze") for that.
I always run vaccumm analyze. The plan only changes in 8.3 after 
"vacuumdb -v -z database".


Sorry, I should have said "bad estimates". That is, because of the 


  j*1.5 BETWEEN 300 AND 400

That's supposed to be that way :)
I think all of this worth for me to have a clue on how the planner goes 
in bad sql, wich causes bad estimates.


Thank you all for your support!

--

[]´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] Planner ignoring to use INDEX SCAN

[Richard Huxton]

Ashish Karalkar wrote:

query which was taking seconds on the join of these two table
suddenly started taking 20/25 min


Show the EXPLAIN ANALYSE of your problem query and someone will be able 
to tell you why.


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

Re: [GENERAL] HouseKeeping and vacuum Questions

[Ow Mun Heng]

On Fri, 2007-12-14 at 09:35 +0100, Harald Armin Massa wrote:
> Ow Mun Heng,
> 
> The current issue which prompted me to do such housekeeping is
> due to 
> long database wide vacuum time. (it went from 2 hours to 4
> hours to 7
> hours)
> 
> If vacuum takes to long, you are doing it not often enough. You should
> use autovacuum, you should be able to express the delta between two
> vacuums in seconds or minutes, not hours or days. 

autovacuum is already turned on and the threshold is at default
settings.

Perhaps I need to tune it to be more aggressive? how can I do that?

delta between 2 vacuums in seconds or minutes? that would be nice. 

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

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

Re: [GENERAL] HouseKeeping and vacuum Questions

[Harald Armin Massa]

Ow Mun Heng,

The current issue which prompted me to do such housekeeping is due to
> long database wide vacuum time. (it went from 2 hours to 4 hours to 7
> hours)
>

If vacuum takes to long, you are doing it not often enough. You should use
autovacuum, you should be able to express the delta between two vacuums in
seconds or minutes, not hours or days.

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!