Re: [GENERAL] HELP FOR LOADING a .psql file (same question again but explained neatly)

[Richard Huxton]

akshay bhat wrote:

hello i am new to psql or any database stuff.
i have downloaded an .psql file from internet and wish to open it and see
the data inside.
i am working on windows xp and have installed the software successfully.
please help i am my wits end.


So what have you tried so far?
What went wrong?
What error messages did you get?
Does this ".psql" file create the database or does it expect you to load 
it into an existing database?


--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] on update, how to change the value?

[A B]

Hi.
If I have table A (x integer  primary key);
and table B (y integer references A on delete cascade );

and that a new item (x=70) gets inserted into A and a lot of items go into B
that references the new item in A.
Now, if I really have to do:

delete from A where x=5;
update A set x=5 where x=70;

is there a way to make all the items in B change from 70 to 5 automatically
(it must be done automatically since B can be a lot more than a signle
table)?

There is an  "on update" option to the create table command, but I can't
find out if that will help me. The docs are unfortunatly a little short on
describing the "on update" option.

Will "on update cascade" propagate the update to the B table?

[GENERAL] debug nonstandard use of \\ in a string literal

[Ivan Zolotukhin]

Hello,

>From time to time I face with these well-known warnings in the
PostgreSQL log, i.e.

Feb 28 04:21:10 db7 postgres[31142]: [2-1] WARNING:  nonstandard use
of escape in a string literal at character 62
Feb 28 04:21:10 db7 postgres[31142]: [2-2] HINT:  Use the escape
string syntax for escapes, e.g., E'\r\n'.

This is fine, everybody knows about that and our PL/PgSQL developers
try to make use of escape syntax. But sometimes errors occur anyway
(by developers mistakes or something). So the question is: how to
debug these annoying messages when pretty big application causes them?
Is it possible to have a look what exact queries produced them?

--
Regards,
 Ivan

---(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] on update, how to change the value?

[Richard Huxton]

A B wrote:

Hi.
If I have table A (x integer  primary key);
and table B (y integer references A on delete cascade );



There is an  "on update" option to the create table command, but I can't
find out if that will help me. The docs are unfortunatly a little short on
describing the "on update" option.

Will "on update cascade" propagate the update to the B table?


Yes.

--
  Richard Huxton
  Archonet Ltd

---(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] dbi_link and dbi:ODBC

[David Fetter]

On Tue, Feb 26, 2008 at 04:07:23PM +0100, Hermann Muster wrote:
> I have the following problem getting to connect a remote database (MS SQL 
> Server in my case) to PostgreSQL.
>
> I'm using SQL Server 2005 Express Edition. I tried it with the following:
>
> SELECT dbi_link.make_accessor_functions(
> 'dbi:ODBC:DRIVER=SQL Native 
> Client;Server=192.168.10.175;Database=Test;Uid=sa;Pwd=test;MARS_Connection=yes;LongReadLen=8000;LongTruncOk=1',

Hermann,

I haven't tested MS SQL Server with the ODBC driver, but I have used
FreeTDS to good effect with that DBMS.  If you have a test setup I can
use to diagnose this, that would be great.  I have some time this
weekend.  Let me know off-list.

By the way, you'll probably want to file a bug report against DBI-Link
 and join the DBI-Link
mailing list to discuss this, as that's a more appropriate forum for
such discussions :)

Cheers,
David (author of DBI-Link)
> 'sa',
> 'test',
> '---
> AutoCommit: 1
> RaiseError: 1
> LongReadLen: 8000
> LongTruncOk: 1
> ',
> NULL,
> NULL,
> NULL,
> 'Solution'
> );
>
> Unfortunately, I can't get it to work. The following errors occur. Any idea 
> about that?
>
> ERROR: error from Perl function: error from Perl function: DBI 
> connect('DRIVER=SQL Native 
> Client;Server=192.168.10.175;Database=Test;Uid=sa;Pwd=test;MARS_Connection=yes;LongReadLen=8000;LongTruncOk=1','sa',...)
>  
> failed: [Microsoft][SQL Native Client]Named Pipes-Provider: A connection to 
> SQL Server couldn't be established [2]. (SQL-08001)
> [Microsoft][SQL Native Client]Logintimeout expired. (SQL-HYT00)
> [Microsoft][SQL Native Client]Invalid attribute for the connection string 
> (SQL-01S00)
> [Microsoft][SQL Native Client]Error with extablishing connection to server. 
> (SQL-08001)(DBD: db_login/SQLConnect err=-1) at line 37 at line 35.
> SQL Status:XX000
>
> P.S. I translated the above messages from german, so it's not exactly the 
> same message than the english SQL Server.
>
> Thanks for your help. :-)
>
> Regards.
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [GENERAL] debug nonstandard use of \\ in a string literal

[Albe Laurenz]

Ivan Zolotukhin wrote:
> From time to time I face with these well-known warnings in the
> PostgreSQL log, i.e.
> 
> Feb 28 04:21:10 db7 postgres[31142]: [2-1] WARNING:  nonstandard use
> of escape in a string literal at character 62
> Feb 28 04:21:10 db7 postgres[31142]: [2-2] HINT:  Use the escape
> string syntax for escapes, e.g., E'\r\n'.
> 
> This is fine, everybody knows about that and our PL/PgSQL developers
> try to make use of escape syntax. But sometimes errors occur anyway
> (by developers mistakes or something). So the question is: how to
> debug these annoying messages when pretty big application causes them?
> Is it possible to have a look what exact queries produced them?

All I can think of is to set

log_statement=all
log_min_error_statement=WARNING
log_min_messages=WARNING

which will cause all statements and warnings to be logged.

This might of course generate a lot of output...

Yours,
Laurenz Albe

---(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] Regarding interval conversion functions and a seeming lack of usefulness

[Alban Hertroys]

On Feb 27, 2008, at 3:47 PM, Bill Moran wrote:

Something like:
$ SELECT CONVERT('12 days 13 hours'::INTERVAL AS hour);
 hour
--
   301
$ SELECT CONVERT('6 hours 17 minutes'::INTERVAL AS hour);
 hour
--
6.2833

Am I approaching this problem wrong? or is there something out there
and my Google skills are lacking?


One of the obvious problems with this is that you cannot convert  
months to something more fine-grained without knowing the date the  
interval is relative to. I mean, what would be the answer of:


$ SELECT CONVERT('2 months'::INTERVAL AS days);

This month that would be 60 days, next month 61, this month next year  
59, etc.
And I haven't even started on leap seconds and daylight saving time  
yet...


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47c69dd2233091191611641!



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

Re: [GENERAL] How to "paste two tables side-by-side"?

[Sam Mason]

On Wed, Feb 27, 2008 at 07:39:51AM -0500, Kynn Jones wrote:
> Suppose I have two tables, A and B, with k(A) and k(B) columns respectively,
> and let's assume to begin with that they have the same number of rows r(A) =
> r(B) = r.
> What's the simplest way to produce a table C having r rows and k(A) + k(B)
> columns, and whose i-th row consists of the k(A) columns of the i-th row of
> A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)?  (By
> "i-th row of A" I mean the i-th row of the listing one would get from
> "SELECT * FROM A", and likewise for B.)
> The question could be generalized slightly to the case where the numbers of
> rows r(A) and r(B) are not equal.  For example, if r(A) < r(B), the desired
> table C would have r(B) rows, and the first k(A) columns of its last r(B) -
> r(A) rows would be nulls, reminiscent of a table produced by a right outer
> join.
> 
> Also, what's the technical term for this type of operation on two tables?

As Erik said, what you're doing doesn't sound like something you'd,
directly, ever want to do in a database---because relational algebra
doesn't have any implied ordering to rely on when doing the indexing, a
fact that Postgres and most databases exploit.

What you're doing sounds a bit like arrays containing some datatype, if
so why not express them (where said datatype is text) as:

  CREATE TABLE a ( idx INTEGER PRIMARY KEY, value TEXT );
  CREATE TABLE b ( idx INTEGER PRIMARY KEY, value TEXT );

"idx" being your "i" above.  It's then trivial to do:

  SELECT COALESCE(a.idx,b.idx) AS idx,
a.value AS a, b.value AS b
  FROM a FULL OUTER JOIN b USING (idx);

to get all the values out.


  Sam

---(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] UUID-OSSP for windows.

[Michael Glaesemann]

On Feb 27, 2008, at 16:59 , Tim Uckun wrote:


 citext is not part of core PostgreSQL or something we have any
 intention to include in the Windows distribution at this time.


Is there an alternative for people wanting a case insensitive  
collation?


ORDER BY lower(foo) ?

Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] Can't start Postgres anymore

[Merlin Moncure]

On Wed, Feb 27, 2008 at 4:08 PM, George Younan <[EMAIL PROTECTED]> wrote:
> I have Postgres installed as a service on Windows xp pro. It has been
> working fine but suddenly i couldn't start it anymore. I looked in different
> forums for a similar problem but didn't find any solution sofar. So any help
> is very appreciated. one more thing to say is that i am a beginner with
> Postgres and don't have much administrative experience...
> As i said above, i can't start my Postgres anymore. I always get the error
> message "could not connect to server: Connection refused
> (0x274D/10061)Is the server running on host "???" and accepting TCP/IP
> connections on port 5432?".
> I checked if the service is running, but it wasn't running. I tried to start
> ist but i couldn't. Doing this from the console i got the following error:
> "C:\Programme\PostgreSQL\8.1\bin>pg_ctl.exe runservice -N "pgsql-8.1" -D
> "C:\Programme\PostgreSQL\8.1\data\"
> pg_ctl: could not start service "pgsql-8.1": error code 1063"
>
>  I chekced postgres.conf and the configuration is as follows:
> # - Connection Settings -
> listen_addresses = 'localhost'
> port = 5432
> max_connections = 100
>
> The last log file is:
> 2008-02-08 07:40:38 LOG:  could not connect socket for statistics collector:
> Es konnte keine Verbindung hergestellt werden, da der Zielcomputer die
> Verbindung verweigerte.
>
>
> 2008-02-08 07:40:38 LOG:  disabling statistics collector for lack of working
> socket
> 2008-02-08 07:40:38 WARNING:  autovacuum not started because of
> misconfiguration
> 2008-02-08 07:40:38 HINT:  Enable options "stats_start_collector" and
> "stats_row_level".
> 2008-02-08 07:40:40 LOG:  database system was shut down at 2008-02-08
> 07:37:57 Westeuropäische Normalzeit
> 2008-02-08 07:40:40 LOG:  checkpoint record is at 0/45AE50
> 2008-02-08 07:40:40 LOG:  redo record is at 0/45AE50; undo record is at 0/0;
> shutdown TRUE
> 2008-02-08 07:40:40 LOG:  next transaction ID: 5100; next OID: 16515
> 2008-02-08 07:40:40 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
> 2008-02-08 07:40:41 LOG:  database system is ready
> 2008-02-08 07:40:41 LOG:  transaction ID wrap limit is 2147484148, limited
> by database "postgres"

>  I'm using Zonealarm firewall but Postgres is authorized on 127.0.0.1:5432.
> It's been working fine for the last month since installtion and i didn't
> change any configuration. I stopped both zonealarm and the windows firewall
> but still success, so i guess it's not a firewall problem.

I highly doubt you have any data loss.  This line makes me suspect the
firewall:
> 2008-02-08 07:40:38 LOG:  disabling statistics collector for lack of working
> socket

Also the log suggests the database is running:
> 2008-02-08 07:40:41 LOG:  database system is ready

ISTM the problem is basically a connection issue.  Try changing
listen_addresses = 'localhost'
to
listen_addresses = '*'

(this will allow postgresql to serve connections from any interface on
the box).   Also try as Gevik suggested running pg_ctl directly.

merlin

---(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] what happens if something goes wrong in transaction?

[A B]

Hi. newbie question, but what will happen if I do

begin work;
select ...
insert ...
and so on...
commit

and somewhere a query fails. will I get an automatic rollback? If not, is
there a way to get that behaviour?
I'm using php to make all these calls and they have all to be succesfull or
no one of them should be carried out.

Re: [GENERAL] beginner postgis question lat/lon

[Martijn van Oosterhout]

On Wed, Feb 27, 2008 at 04:59:07PM -0800, shadrack wrote:
> postgis=# insert into routes_geom values(1, 'J084',
> GeomFromText('LINESTRING(38.20 -121.00, 38.20, -118.00)', 4326));
> 
> I receive this error:
> ERROR:  parse error - invalid geometry
> CONTEXT:  SQL function "geomfromtext" statement 1

You have an extraneous comma after the 38.20.

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] what happens if something goes wrong in transaction?

[Alvaro Herrera]

A B escribió:
> Hi. newbie question, but what will happen if I do
> 
> begin work;
> select ...
> insert ...
> and so on...
> commit
> 
> and somewhere a query fails. will I get an automatic rollback?

Of course.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] beginner postgis question lat/lon

[Michael Fuhr]

On Wed, Feb 27, 2008 at 04:59:07PM -0800, shadrack wrote:
> This may seem like a very simple question...it is...but I can't find
> documentation on it to help. I've seen some posts about lat/long but
> none that give simple solutions on how to insert lat/long in tables.

See the PostGIS documentation, in particular Chapter 4 "Using PostGIS":

http://postgis.refractions.net/docs/ch04.html

(The site isn't responding right now; hopefully it'll be available
soon.)

> postgis=# insert into routes_geom values(1, 'J084',
> GeomFromText('LINESTRING(38.20 -121.00, 38.20, -118.00)', 4326));
> 
> I receive this error:
> ERROR:  parse error - invalid geometry
> CONTEXT:  SQL function "geomfromtext" statement 1

There are two problems with the geometry string: the syntax error is
due an extra comma in the second pair of coordinates, and coordinates
should be (X Y) therefore (lon lat) instead of (lat lon).  Try this:

insert into routes_geom values(1, 'J084', GeomFromText('LINESTRING(-121.00 
38.20, -118.00 38.20)', 4326));

You might wish to subscribe to the postgis-users mailing list if you
have additional questions.

-- 
Michael Fuhr

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

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

Re: [GENERAL] what happens if something goes wrong in transaction?

[Michael Fuhr]

On Thu, Feb 28, 2008 at 02:19:01PM +, A B wrote:
> Hi. newbie question, but what will happen if I do
> 
> begin work;
> select ...
> insert ...
> and so on...
> commit
> 
> and somewhere a query fails. will I get an automatic rollback?

After the error every subsequent statement will fail with "ERROR:
current transaction is aborted, commands ignored until end of
transaction block."  The transaction doesn't automatically end but
it will roll back even if you try to commit (assuming you didn't
do a partial rollback with SAVEPOINT/ROLLBACK TO).

> I'm using php to make all these calls and they have all to be succesfull or
> no one of them should be carried out.

That's the behavior you'll get if you use a transaction.  No changes
will be visible to other transactions until you successfully commit.

-- 
Michael Fuhr

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

   http://archives.postgresql.org/

[GENERAL] beginner: what permissions required to install on windows 2000+

[Dee]

What are the permissions required to install postgres as a service on windows 
2000/2003?  I have followed the instructions, as I understand them, but the 
program will not install. 

It either fails with "The application failed to initialize properly 
(0xc022). Click on OK to terminate the application."  or "Invalid username 
specified: A required privlege is not held by the client".  


 
   
-
Never miss a thing.   Make Yahoo your homepage.

Re: [GENERAL] disabling triggers, constaints and so on

[Erik Jones]

On Feb 25, 2008, at 2:33 PM, Geoffrey wrote:

We are still in a pickle with trying to resolve our trigger issues  
without affecting slony triggers.


The point is, we need to be able to disable triggers, check  
constraints, and foreign-key constraints without affecting slony  
triggers in certain situations.


This is all running on 7.4.19, thus, it's our understanding that  
using tgenabled is not going to be a solution.


Making our triggers smarter doesn't get us all the way there.

Suggestions?


This may've been suggested to you earlier, but can you drop them and   
re-add them when you're done?


Erik Jones

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

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




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

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

Re: [GENERAL] Regarding interval conversion functions and a seeming lack of usefulness

[Erik Jones]

On Feb 28, 2008, at 6:04 AM, Alban Hertroys wrote:


On Feb 27, 2008, at 3:47 PM, Bill Moran wrote:

Something like:
$ SELECT CONVERT('12 days 13 hours'::INTERVAL AS hour);
 hour
--
   301
$ SELECT CONVERT('6 hours 17 minutes'::INTERVAL AS hour);
 hour
--
6.2833

Am I approaching this problem wrong? or is there something out there
and my Google skills are lacking?


One of the obvious problems with this is that you cannot convert  
months to something more fine-grained without knowing the date the  
interval is relative to. I mean, what would be the answer of:


$ SELECT CONVERT('2 months'::INTERVAL AS days);

This month that would be 60 days, next month 61, this month next  
year 59, etc.
And I haven't even started on leap seconds and daylight saving time  
yet...


Typically, even having the option to use functions such of these with  
"standard" measurments (i.e. 30 days = 1 month, 365 days = 1 year,  
etc...) is often really useful.  Observe that the justify_days,  
justify_hours and justify_interval already work with 30 days and 24  
hour increments, respectively, they just don't give you much control  
over the units used in the return value.  In fact, I'd even say that  
the names of justify_days and justify_hours are confusing.  Perhaps  
something like justify_to(some_interval, some_time_unit) would be  
useful?


Erik Jones

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

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




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

[GENERAL] initlocation on windows

[conor.mccarthy]

I'm trying to use initlocation to create a database on a second hard disk
using a windows installation of postgresql. I can't find this initlocation
program anywhere. Is it possible to do on Windows? Can someone print me to a
copy of initlocation or tell me what I'm doing wrong please.

Many thanks

Conor
-- 
View this message in context: 
http://www.nabble.com/initlocation-on-windows-tp15739520p15739520.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

   http://archives.postgresql.org/

Re: [GENERAL] HELP FOR LOADING a .psql file (same question again but explained neatly)

[Colin Wetherbee]

akshay bhat wrote:

hello i am new to psql or any database stuff.
i have downloaded an .psql file from internet and wish to open it and 
see the data inside.

i am working on windows xp and have installed the software successfully.
please help i am my wits end.
it is huge file 800mb
and is supposed to contain a database.
the question is how to load it?

it was downloaded from this link http://conceptnet.media.mit.edu/
the description says
The ConceptNet 3 database 
, as a 
PostgreSQL input file. You will need to be running a PostgreSQL server 
to install ConceptNet 3.


how do i load it?


It was a 40-second download, so I grabbed it.

It looks like you need to create a database and then load this .psql 
file into it.  It contains all your CREATE TABLE statements and 
everything else.


Once you get your database set up appropriately, this command should do 
the trick:


psql -d  -f conceptnet-2007-09-25.psql

Colin

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

[GENERAL] "=" or ":=" ?

[BERTRAND Joël]

Hello,

	I'm trying to optimize assign_vertex_id() function provided by 
pgRouting/PostGIS. In this function, I can see :


DECLARE
  points record;
  i record;
  source_id int;
  target_id int;
  pre varchar;
  post varchar;

  srid integer;

  BEGIN

BEGIN
DROP TABLE vertices_tmp;
EXCEPTION
WHEN UNDEFINED_TABLE THEN
END;

EXECUTE 'CREATE TABLE vertices_tmp (id serial)';

		FOR i IN EXECUTE 'SELECT srid FROM geometry_columns WHERE 
f_table_name='''|| quote_ident(geom_table)|| LOOP

END LOOP;

srid := i.srid;

		EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', 
'||srid||', ''POINT'', 2)';
			 

CREATE INDEX vertices_tmp_idx ON vertices_tmp USING 
GIST (the_geom);


pre = '';
post = '';

	I don't understand last assignations. In pgsql documentation, ther is 
written that all assignations have to be written with ":=", not with 
"=". What is the difference between "=" and ":=" ? I don't find any 
information about "=".


Regards,

JKB

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

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

Re: [GENERAL] initlocation on windows

[Alvaro Herrera]

conor.mccarthy escribió:
> 
> I'm trying to use initlocation to create a database on a second hard disk
> using a windows installation of postgresql. I can't find this initlocation
> program anywhere. Is it possible to do on Windows? Can someone print me to a
> copy of initlocation or tell me what I'm doing wrong please.

initlocation hasn't existed since a few years.  The current way to do
what you want is to use CREATE TABLESPACE:
http://www.postgresql.org/docs/current/interactive/sql-createtablespace.html

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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

Re: [GENERAL] "=" or ":=" ?

[Alvaro Herrera]

BERTRAND Joël wrote:

>   I don't understand last assignations. In pgsql documentation, ther is  
> written that all assignations have to be written with ":=", not with  
> "=". What is the difference between "=" and ":=" ? I don't find any  
> information about "=".

It's exactly the same.  := is the documented way, but = is also
accepted and behaves identically.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

   http://archives.postgresql.org/

Re: [GENERAL] Can't start Postgres anymore

[Andrew Sullivan]

On Thu, Feb 28, 2008 at 08:38:26AM -0500, Merlin Moncure wrote:
> 
> >  I'm using Zonealarm firewall but Postgres is authorized on 127.0.0.1:5432.

Sorry, I missed this in the OP.  But I've had people tell me that Zonealarm
causes them problems even if it's completely turned off.  One person told me
that he had to uninstall it to get pg_dump to work properly.  Dunno if that
helps -- I'm no windows guy.

A


---(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] "=" or ":=" ?

[Tom Lane]

=?ISO-8859-1?Q?BERTRAND_Jo=EBl?= <[EMAIL PROTECTED]> writes:
> What is the difference between "=" and ":=" ?

None; plpgsql accepts either for assignment.

regards, tom lane

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

[GENERAL] WAL Log Size

[John Evans]

All,

  I've done some searching in the mailing list archives, and the
Internet in general, and come up with a blank. Here's my setup:

  I'm working on setting up a replication system between two 8.1.11
servers using WAL log shipping via rsync. Nothing special there, but
the problem that I'm having is that a new WAL log will not be created
(and thus shipped) until 16MB of transactional data is created. The
database that I am setting things up on does not produce a vast amount of
transactions, and it can sometimes take over an hour before a new log is
generated. My boss wants a smaller window between WAL logs.

  How can I change the WAL log size from 16MB to something smaller?
Ideally, I would like to shoot for 8MB or even 4MB.

  The only thing that I've found is to hack the code to change all
instances of YY_READ_BUF_SIZE from 16777216 to 4194304, but I'm not sure
what else that will affect, if anything.

  I've heard that upgrading to 8.2 or 8.3 will allow me to setup a
timeout value for WAL log creation, but upgrading at this time is not an
option for various reasons.

  Any insight that you can provide will be greatly appreciated!

--
John Evans
Administrator of kilnar.com

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

  http://archives.postgresql.org/

Re: [GENERAL] initlocation on windows

[conor.mccarthy]

Ah ha. Great, thanks.

Alvaro Herrera-7 wrote:
> 
> conor.mccarthy escribió:
>> 
>> I'm trying to use initlocation to create a database on a second hard disk
>> using a windows installation of postgresql. I can't find this
>> initlocation
>> program anywhere. Is it possible to do on Windows? Can someone print me
>> to a
>> copy of initlocation or tell me what I'm doing wrong please.
> 
> initlocation hasn't existed since a few years.  The current way to do
> what you want is to use CREATE TABLESPACE:
> http://www.postgresql.org/docs/current/interactive/sql-createtablespace.html
> 
> -- 
> Alvaro Herrera   
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
> 

-- 
View this message in context: 
http://www.nabble.com/initlocation-on-windows-tp15739520p15740840.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] WAL Log Size

[Alan Hodgson]

On Thursday 28 February 2008, John Evans <[EMAIL PROTECTED]> wrote:
>I've heard that upgrading to 8.2 or 8.3 will allow me to setup a
> timeout value for WAL log creation, but upgrading at this time is not an
> option for various reasons.
>
>Any insight that you can provide will be greatly appreciated!

Write a script that does "something" that results in 16MB of WAL logging and 
run it whenever you want a rotation to occur. Inserting a few hundred 
thousand rows into an otherwise empty table should work. If it's not 
indexed it won't impact your server much, especially if your normal 
transaction volume is that low.


-- 
Alan

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

   http://archives.postgresql.org/

Re: [GENERAL] "=" or ":=" ?

[BERTRAND Joël]

Tom Lane wrote:

=?ISO-8859-1?Q?BERTRAND_Jo=EBl?= <[EMAIL PROTECTED]> writes:

What is the difference between "=" and ":=" ?


None; plpgsql accepts either for assignment.


Thank you for your answer. I suggest to add a note in documentation ;-)

Regards,

JKB

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

Re: [GENERAL] WAL Log Size

[Erik Jones]

On Feb 28, 2008, at 12:51 PM, Alan Hodgson wrote:


On Thursday 28 February 2008, John Evans <[EMAIL PROTECTED]> wrote:

   I've heard that upgrading to 8.2 or 8.3 will allow me to setup a
timeout value for WAL log creation, but upgrading at this time is  
not an

option for various reasons.

   Any insight that you can provide will be greatly appreciated!


Write a script that does "something" that results in 16MB of WAL  
logging and

run it whenever you want a rotation to occur. Inserting a few hundred
thousand rows into an otherwise empty table should work. If it's not
indexed it won't impact your server much, especially if your normal
transaction volume is that low.


Or, even simpler:

SELECT pg_switch_xlog();

Erik Jones

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

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




---(end of broadcast)---
TIP 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] "=" or ":=" ?

[Jeff Davis]

On Thu, 2008-02-28 at 20:11 +0100, BERTRAND Joël wrote:
> Tom Lane wrote:
> > =?ISO-8859-1?Q?BERTRAND_Jo=EBl?= <[EMAIL PROTECTED]> writes:
> >> What is the difference between "=" and ":=" ?
> > 
> > None; plpgsql accepts either for assignment.
> 
>   Thank you for your answer. I suggest to add a note in documentation ;-)

I think that it is undocumented on purpose.

"=" in SQL is generally for testing equality, and having one operator
mean two completely different things can be confusing. Therefore, ":="
is encouraged.

Regards,
Jeff Davis


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

Re: [GENERAL] WAL Log Size

[Alan Hodgson]

On Thursday 28 February 2008, Erik Jones <[EMAIL PROTECTED]> wrote:
> Or, even simpler:
>
> SELECT pg_switch_xlog();

The original poster is using 8.1.


-- 
Alan

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

[GENERAL] partitioning using dblink

[Scara Maccai]

Hi,

I started thinking that using dblink I could "easily" get some kind of read 
only multi-server partitioning, if only VIEWs could be declared with 
"INHERITS"...

That way I think I could 

1) add as many views as the number of DBs as

CREATE VIEW mytable_part_n AS

INHERITS mytable

to every DB I have

2) A select on the DB that asks for data on multiple DBs (because it uses data 
from different partition) would ask the proper data to the proper server...


I think that it would be very nice... 
But, since VIEWs can't be declared using INHERITS, that won't work...

Am I wrong?



I know that putting INHERITS and CHECKs on the VIEWs are not a good idea, but I 
think some method to declare a TABLE as being "remote" would be very cool... I 
don't know, maybe using a new "storage_parameter"...








  ___ 
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: 
http://it.docs.yahoo.com/nowyoucan.html

[GENERAL] syntax errors at "line 1" when executing every command

[Steven De Vriendt]

Hi list,

I'm using the latest postgreSQL with the lastest postgis.
When executing every command I want to use, I get the following error
message:

postgis=# createdb;
ERROR:  syntax error at or near "createdb"
LINE 1: createdb;
^

I can use psql to reach my database, but after that I'm finished.
Can't use any command. I thought this was a Vista (PATH) issue
so I turned to Windows XP, however, I encounter the exact
same issue. What am I doing wrong ??


Thx
Steven

Re: [GENERAL] syntax errors at "line 1" when executing every command

[Colin Wetherbee]

Steven De Vriendt wrote:

I'm using the latest postgreSQL with the lastest postgis.
When executing every command I want to use, I get the following error 
message:
 
postgis=# createdb;

ERROR:  syntax error at or near "createdb"
LINE 1: createdb;


What other commands are you trying?

"createdb" is not a valid SQL or psql command.  You're looking for 
CREATE DATABASE... or the "createdb" command line tool.


Colin

---(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] partitioning using dblink

[Alvaro Herrera]

Scara Maccai wrote:

> I started thinking that using dblink I could "easily" get some kind of
> read only multi-server partitioning, if only VIEWs could be declared
> with "INHERITS"...

I think you can do pretty much the same thing with PL/Proxy; see
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(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] "Consider compacting this relation..." ???

[Kynn Jones]

I just ran VACUUM ANALYZE and got this warning I've never seen before:

WARNING:  relation "public.some_big_table" contains more than
"max_fsm_pages" pages with useful free space
HINT:  Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".


What does the hint mean by "compacting"?  What exactly must I do to compact
this table?

Also, at the end of the VACUUM ANALYZE run I get the NOTICE:

NOTICE:  number of page slots needed (294528) exceeds max_fsm_pages (153600)
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a
value over 294528.

What surprises me here is that even though I've never seen this notice
before (and I've been running VACUUM ANALYZE a lot lately!), the number of
page slots needed is almost twice as much as the value of max_fsm_pages.
 What could I have done since the last time I ran VACUUM ANALYZE (not more
than 24 hours ago) to make this "page slot" requirement to shoot through the
roof like this?

Thanks in advance!

kynn

Re: [GENERAL] WAL Log Size

[Erik Jones]

On Feb 28, 2008, at 1:58 PM, Alan Hodgson wrote:


On Thursday 28 February 2008, Erik Jones <[EMAIL PROTECTED]> wrote:

Or, even simpler:

SELECT pg_switch_xlog();


The original poster is using 8.1.


Ah, I didn't realize that was only available in >= 8.2, thanks for  
the clarification.


Erik Jones

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

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




---(end of broadcast)---
TIP 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] syntax errors at "line 1" when executing every command

[Michael Glaesemann]

On 2008-02-28, at 3:42 PM, Steven De Vriendt wrote:


Hi list,

I'm using the latest postgreSQL with the lastest postgis.
When executing every command I want to use, I get the following  
error message:


postgis=# createdb;
ERROR:  syntax error at or near "createdb"
LINE 1: createdb;



createdb is a command line application. You're looking for the CREATE  
DATABASE SQL command:


http://www.postgresql.org/docs/8.3/interactive/sql-createdatabase.html

Michael Glaesemann
[EMAIL PROTECTED]




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

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

Re: [GENERAL] WAL Log Size

[Greg Smith]

On Thu, 28 Feb 2008, John Evans wrote:

How can I change the WAL log size from 16MB to something smaller? 
Ideally, I would like to shoot for 8MB or even 4MB. The only thing that 
I've found is to hack the code to change all instances of 
YY_READ_BUF_SIZE from 16777216 to 4194304, but I'm not sure what else 
that will affect, if anything.


Nope; you'd want to play with XLOG_SEG_SIZE to change this.  Have to 
dump/initdb/reload your database as well to do it.  Really just not a good 
idea.



 I've heard that upgrading to 8.2 or 8.3 will allow me to setup a
timeout value for WAL log creation, but upgrading at this time is not an
option for various reasons.


Yes, the archive_timeout feature introduced into 8.2 is the one you want 
but don't have yet.


Courtesy of Simon ( 
http://archives.postgresql.org/pgsql-general/2007-06/msg00015.php ) you 
can force 16MB worth of WAL activity that doesn't leave any changes behind 
with:


create table xlog_switch as
select '0123456789ABCDE' from generate_series(1,100);
drop table xlog_switch;

Pop that into cron etc. via psql and you can make the window for log 
shipping as fine as you'd like even with no activity.  I'd guess that 
somewhere around every 5 minutes is as often as you'd want to abuse the 
WAL features with this hack, if you do it too often you're increasing te 
odds it will interfere with real transactions.


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

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

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

Re: [GENERAL] partitioning using dblink

[Scara Maccai]

Alvaro Herrera wrote:

> I think you can do pretty much the same thing with
PL/Proxy; see
>
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

Mmmh, I actually looked into that but I thought it
only worked with user functions...
am I wrong?

What I'd like to have is an almost-transparent
horizontal partitioning system, and
I think that everything is there: postgresql
partitioning (which even has partition pruning)
 + dblink should be enough...

it's only that you can't use them together, because
with dblink you should use
VIEWs but partitioning can't work with those (which I
find correct, BTW).
What I would like is for Postgresql to know that a
table is actually a remote table...

BUT!!!
since "Views in PostgreSQL are implemented using the
rule system", I could do
(pseudo-sql)

CREATE TABLE mypartion HINERITS blabla CHECK
CONSTR..[...] ;

CREATE RULE "_RETURN" AS ON SELECT TO myview DO
INSTEAD
SELECT * FROM dblink;

Can't I??? That would be horizontal partitioning using
dblink+postgresql!!!

Am I wrong???


  ___ 
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: 
http://it.docs.yahoo.com/nowyoucan.html

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

[GENERAL] Space wasted by denormalized data

[Decibel!]

I wrote this query to identify how much space is being wasted in a
database by denormalized data. For each field in each table, it
calculates how much space the field is taking in it's table as well as
indexes, then it calculates how much space would be needed if that field
was normalized into a separate table. It places some (somewhat
arbitrary) minimums on how much space would have to be saved to include
that field in the output. If you want to get rid of the limit you should
still keep savings > 0 in the query, otherwise you'll start seeing
normalization suggestions that make no sense (like normalizing an int).

As it stands, the query reports a total for the entire database, but you
can use just sub-sections of the query to see savings per-table, or
per-field.

-- Total
SELECT pg_size_pretty(sum(sum)::bigint)
-- Summarize by table
FROM (SELECT schemaname, tablename, sum(savings), 
pg_size_pretty(sum(savings)::bigint)
-- Get pretty size. Start here if you want per-table info
FROM (SELECT *, pg_size_pretty(savings::bigint)
FROM (
-- Here's where the real work starts
SELECT *, table_space_delta + coalesce(index_space_delta, 0) - side_table_space 
AS savings
FROM (
-- Figure out how much space we'd save in indexes by converting to an 
int
SELECT *, index_tuple_count*(avg_width-4) AS index_space_delta
FROM (SELECT s.*, index_count, index_tuple_count
FROM (SELECT schemaname, tablename, attname, null_frac, 
avg_width, n_distinct
-- How much space would we gain by 
changing this field to an int?
, 
reltuples*(1-null_frac)*(avg_width-4) AS table_space_delta
-- Estimate how big our "side 
table" will be
, CASE WHEN n_distinct >= 0 THEN 
n_distinct ELSE -n_distinct * reltuples END
* 
(24+4+avg_width+6+4+6+avg_width) AS side_table_space
FROM pg_stats s
JOIN pg_class c ON c.relname=tablename AND 
c.relkind='r'
JOIN pg_namespace n ON n.oid = 
c.relnamespace AND n.nspname=s.schemaname
WHERE schemaname NOT IN 
('pg_catalog','information_schema')
) s
NATURAL LEFT JOIN (
SELECT n.nspname AS schemaname, c.relname 
AS tablename, attname
, count(*) AS index_count, 
sum(i.reltuples) AS index_tuple_count
FROM pg_index x
JOIN pg_class c ON c.oid = 
x.indrelid
JOIN pg_class i ON i.oid = 
x.indexrelid
JOIN pg_namespace n ON n.oid = 
c.relnamespace
JOIN pg_attribute a ON a.attrelid = 
i.oid
GROUP BY n.nspname, c.relname, attname
) i
) a
) a
) a
-- Minimum savings to consider per-field
WHERE savings > 1e6) a
GROUP BY schemaname, tablename
-- Minimum savings to consider per-table
HAVING sum(savings) > 1e7
ORDER BY sum(savings) DESC
) a
;
-- 
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828


pgpPK8JSpK8sA.pgp
Description: PGP signature

Re: [GENERAL] syntax errors at "line 1" when executing every command

[Thomas Kellerer]

Steven De Vriendt wrote on 28.02.2008 21:42:

Hi list,

I'm using the latest postgreSQL with the lastest postgis.
When executing every command I want to use, I get the following error
message:

postgis=# createdb;
ERROR:  syntax error at or near "createdb"
LINE 1: createdb;
^

I can use psql to reach my database, but after that I'm finished.
Can't use any command. I thought this was a Vista (PATH) issue
so I turned to Windows XP, however, I encounter the exact
same issue. What am I doing wrong ??


createdb is an OS-level command, it is not a psql command!
The equivalent SQL command is "CREATE DATABASE"

Thomas


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

Re: [GENERAL] Space wasted by denormalized data

[Colin Wetherbee]

Decibel! wrote:
I wrote this query to identify how much space is being wasted in a 
database by denormalized data. For each field in each table, it 
calculates how much space the field is taking in it's table as well

as indexes, then it calculates how much space would be needed if that
field was normalized into a separate table. It places some (somewhat 
arbitrary) minimums on how much space would have to be saved to

include that field in the output. If you want to get rid of the limit
you should still keep savings > 0 in the query, otherwise you'll
start seeing normalization suggestions that make no sense (like
normalizing an int).


That's... really cool.

What does this mean? :)

 pg_size_pretty


(1 row)

Colin

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

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

[GENERAL] LIMIT Question

[Terry Lee Tucker]

When one uses LIMIT, as in LIMIT 1, is the entire query executed on the server 
side, but only one record returned?


 PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 
20060404 (Red Hat 3.4.6-9)

Thanks...
-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com

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

[GENERAL] errors in pg_restore on windows?

[Dan Armbrust]

Using PostgreSQL 8.3 on Windows, I make a backup like this:
pg_dump -U postgres -C -d -D -Fc -f ispaaa-pg.bak ispaaa

Then restore like this:
pg_restore -U postgres -C -d template1 -v -Fc ispaaa-pg.bak

And I get lots of these errors:


pg_restore: creating TABLE voip
pg_restore: creating FUNCTION pldbg_abort_target(integer)
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 21; 1255 16419
FUNCTION pldbg_abort_target(integer) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  could not
load library "C:/Program Files (x86)/PostgreSQL/8.3/lib/pldbgapi.dll":
The specified module could not be found.

Command was: CREATE FUNCTION pldbg_abort_target(session integer) RETURNS SET
OF boolean
AS '$libdir/pldbgapi', 'pldbg_abort_target'
  ...
pg_restore: [archiver (db)] could not execute query: ERROR:  function public.pld
bg_abort_target(integer) does not exist
Command was: ALTER FUNCTION public.pldbg_abort_target(session integer) OWNER
 TO postgres;


It looks like a packaging issue with the windows installer - these
commands work fine for me on Linux.

Thanks,

Dan

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

Re: [GENERAL] WAL Log Size

[John Evans]

On Thu, 28 Feb 2008, Greg Smith wrote:

Nope; you'd want to play with XLOG_SEG_SIZE to change this.  Have to 
dump/initdb/reload your database as well to do it.  Really just not a good 
idea.


Thanks for the insight there. I figured changing the code would be
dangerous. I'm glad I asked, and thanks for the answer!


create table xlog_switch as
select '0123456789ABCDE' from generate_series(1,100);
drop table xlog_switch;


Thanks for the 2-liner. Good stuff. I'll be putting that into place
ASAP!

--
John Evans
Administrator of kilnar.com

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

Re: [GENERAL] LIMIT Question

[Scott Marlowe]

On Thu, Feb 28, 2008 at 3:20 PM, Terry Lee Tucker <[EMAIL PROTECTED]> wrote:
> When one uses LIMIT, as in LIMIT 1, is the entire query executed on the server
>  side, but only one record returned?
>
>  
> 
>   PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
>  20060404 (Red Hat 3.4.6-9)

Sometimes yes, sometimes no.  Depends on how complex the query is and
whether or not pgsql's query planner can see a shortcut or not.  It's
more likely that a later version will have the optimizations needed to
do that than an older version like 7.4 I'd think.  But I'd ask someone
more expert on the planner like Tom to be sure.

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

   http://archives.postgresql.org/

Re: [GENERAL] "Consider compacting this relation..." ???

[Scott Marlowe]

On Thu, Feb 28, 2008 at 3:14 PM, Kynn Jones <[EMAIL PROTECTED]> wrote:
> I just ran VACUUM ANALYZE and got this warning I've never seen before:
>
>  WARNING:  relation "public.some_big_table" contains more than
> "max_fsm_pages" pages with useful free space
> HINT:  Consider compacting this relation or increasing the configuration
> parameter "max_fsm_pages".
>
>
> What does the hint mean by "compacting"?  What exactly must I do to compact
> this table?

Vacuum full

>  Also, at the end of the VACUUM ANALYZE run I get the NOTICE:
>
> NOTICE:  number of page slots needed (294528) exceeds max_fsm_pages (153600)
> HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a
> value over 294528.
>
>
> What surprises me here is that even though I've never seen this notice
> before (and I've been running VACUUM ANALYZE a lot lately!), the number of
> page slots needed is almost twice as much as the value of max_fsm_pages.
> What could I have done since the last time I ran VACUUM ANALYZE (not more
> than 24 hours ago) to make this "page slot" requirement to shoot through the
> roof like this?

Lots of deletes or updates on the table.

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

Re: [GENERAL] WAL Log Size

[Sam Mason]

On Thu, Feb 28, 2008 at 04:35:44PM -0500, Greg Smith wrote:
> Courtesy of Simon ( 
> http://archives.postgresql.org/pgsql-general/2007-06/msg00015.php ) you 
> can force 16MB worth of WAL activity that doesn't leave any changes behind 
> with:
> 
> create table xlog_switch as
> select '0123456789ABCDE' from generate_series(1,100);
> drop table xlog_switch;

Just out of interest, why doesn't it do the following?

  BEGIN;
  create table xlog_switch as
select '0123456789ABCDE' from generate_series(1,100);
  ROLLBACK;


Thanks,
  Sam

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

[GENERAL] rule question

[Tim Rupp]

Hey list,

Does CREATE RULE require an exclusive lock on the table it's making a 
rule for? For instance, if an insert is being done on the table, and you 
do 'create rule', it will wait for said insert to finish?


Thanks,
-Tim

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

[GENERAL] Text Search zero padding

[Richard Greenwood]

I am using text search across multiple columns. Two of the columns
have values that have zero padding - sort of. The values look like
R0001234 (1 char followed by 7 digits, zero padded). Users are
accustom to searching with and without the zero padding (entering
R0001234 or R1234 should return identical results). This is easy to
accommodate when parsing user input for a single column, but text
searching across multiple columns it is harder determine if a
char/digit group should be padded.

So far my best idea is to create a tsvector column containing both
padded and non-padded versions of the value. i.e. put both R1234 and
R0001234 into the tsvector column. This seems pretty brute force, and
I am pretty new to text search, so I'd welcome any suggestions.

Thanks,
Rich

-- 
Richard Greenwood
[EMAIL PROTECTED]
www.greenwoodmap.com

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

Re: [GENERAL] rule question

[Devi]

Hi,

CREATE RULE dosen't require any lock.  It is carried out in the parser 
level.  But there will be ACCESS SHARE lock over the tables which are being 
queried & are acquired automatically.


Thanks
DEVI.G
- Original Message - 
From: "Tim Rupp" <[EMAIL PROTECTED]>

To: 
Sent: Friday, February 29, 2008 8:47 AM
Subject: [GENERAL] rule question



Hey list,

Does CREATE RULE require an exclusive lock on the table it's making a rule 
for? For instance, if an insert is being done on the table, and you do 
'create rule', it will wait for said insert to finish?


Thanks,
-Tim

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



--
No virus found in this incoming message.
Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 
269.21.1/1302 - Release Date: 2/27/2008 4:34 PM






---(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] Text Search zero padding

[Tom Lane]

"Richard Greenwood" <[EMAIL PROTECTED]> writes:
> I am using text search across multiple columns. Two of the columns
> have values that have zero padding - sort of. The values look like
> R0001234 (1 char followed by 7 digits, zero padded). Users are
> accustom to searching with and without the zero padding (entering
> R0001234 or R1234 should return identical results). This is easy to
> accommodate when parsing user input for a single column, but text
> searching across multiple columns it is harder determine if a
> char/digit group should be padded.

> So far my best idea is to create a tsvector column containing both
> padded and non-padded versions of the value. i.e. put both R1234 and
> R0001234 into the tsvector column. This seems pretty brute force, and
> I am pretty new to text search, so I'd welcome any suggestions.

I'm not an expert in tsearch either, but given what you say here,
it seems like the Right Thing is to create a parser or dictionary
that strips those zeroes as being insignificant, so that R0001234 and
R1234 get mapped to the same stored/searchable lexeme.

regards, tom lane

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

Re: [GENERAL] rule question

[Klint Gore]

[see below or the top posting police will arrive on my doorstep :)]

Devi wrote:

Hi,

CREATE RULE dosen't require any lock.  It is carried out in the parser 
level.  But there will be ACCESS SHARE lock over the tables which are 
being queried & are acquired automatically.


Thanks
DEVI.G
- Original Message - From: "Tim Rupp" <[EMAIL PROTECTED]>
To: 
Sent: Friday, February 29, 2008 8:47 AM
Subject: [GENERAL] rule question



Hey list,

Does CREATE RULE require an exclusive lock on the table it's making a 
rule for? For instance, if an insert is being done on the table, and 
you do 'create rule', it will wait for said insert to finish?


Thanks,
-Tim
Seems to me like needs an exclusive lock.  I setup 2 sessions. first one 
idle in transaction after an insert and then issued the create rule in 
the other.  the 2nd one sat there.


pg_locks in the 1st one said
# select * from pg_locks where relation = 20404;
locktype | database | relation | page | tuple | virtualxid | 
transactionid | classid | objid | objsubid | virtualtransaction | pid  
|mode | granted

--+--+--+--+---++---+-+---+--++--+-+
-
relation |16770 |20404 |  |   |
|   |  |   |  | 1/921  |  632 | 
RowExclusiveLock| t
relation |16770 |20404 |  |   |
|   |  |   |  | 2/771  | 3812 | 
AccessExclusiveLock | f

(2 rows)

(ignore the formatting the important bit is pid, mode, granted) 3812 is 
the pid of my create rule according to pg_backend_pid() and 632 is my 
insert transaction.


Execution of the rule follows what you were saying.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


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

Re: [GENERAL] "Consider compacting this relation..." ???

[Tom Lane]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> On Thu, Feb 28, 2008 at 3:14 PM, Kynn Jones <[EMAIL PROTECTED]> wrote:
>> What does the hint mean by "compacting"?  What exactly must I do to compact
>> this table?

> Vacuum full

CLUSTER is a good alternative also, though pre-8.3 you need to consider
that it's not MVCC-safe; that could be important in heavily concurrent
applications.

regards, tom lane

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

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

Re: [GENERAL] WAL Log Size

[Greg Smith]

On Fri, 29 Feb 2008, Sam Mason wrote:


Just out of interest, why doesn't it do the following?

 BEGIN;
 create table xlog_switch as
   select '0123456789ABCDE' from generate_series(1,100);
 ROLLBACK;


I'm not 100% sure here what happens when you do the above, and it depends 
on version, but there are cases where creating a new or empty table in a 
transaction is optimized to not create any WAL as a performance 
improvement.  This has become a common idiom for that reason:


BEGIN;
truncate table t;
copy t from '/fdsa/fds/afds.csv' with csv;
COMMIT;

To take advantage of this loading without WAL feature.

I wish I had more details here, never have found a more formal definition 
of how this works than suggestions on the list.


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

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

Re: [GENERAL] WAL Log Size

[Scott Marlowe]

On Fri, Feb 29, 2008 at 1:08 AM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Fri, 29 Feb 2008, Sam Mason wrote:
>
>  > Just out of interest, why doesn't it do the following?
>  >
>  >  BEGIN;
>  >  create table xlog_switch as
>  >select '0123456789ABCDE' from generate_series(1,100);
>  >  ROLLBACK;
>
>  I'm not 100% sure here what happens when you do the above, and it depends
>  on version, but there are cases where creating a new or empty table in a
>  transaction is optimized to not create any WAL as a performance
>  improvement.  This has become a common idiom for that reason:

That's why the create table statement up there had the from
generate_series bit...

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