Re: Fwd: [GENERAL] convert(USING utf8_to_iso_8859_15) on Windows

[Martijn van Oosterhout]

On Mon, Jan 29, 2007 at 03:46:34PM -0500, Pierre Thibaudeau wrote:
> * Windows XP does support UTF8, yet it is not possible (as far as I
> know) to define one's locale to have anything to do with UTF8
> (presumably in the sense that UTF8 isn't an aspect of a specific
> locale):  there is no en_US.UTF8 or fr_CA.UTF8 locales, for instance.
> But why should this matter?  Say I am entering the data through a
> piece of software that works with UTF8, via the ODBC driver.  Say
> again that I output the data with another software that expects UTF8,
> via the JDBC driver.  Why does it matter that my system should be
> localized in another encoding?

Because postgresql relies on OS support to do things like string
comparison. Since Windows does not support UTF-8 locales, sorting there
with UTF-8 is a bit of a hack, whereas many (some?) Unixes can handle
it natively.

At some point postgresql will know how to do string comparisons itself
ad thus the problem will be solved, but it hasn't happened yet.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Locking question?

[Shoaib Mir]

There is such timeout from the database server for the idle connections but
yes you can always use firewall settings in order to do that and kill idle
connections.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/6/07, Gurjeet Singh <[EMAIL PROTECTED]> wrote:


On 1/30/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> Shoaib Mir wrote:
> > While debugging an application, I just wanted to confirm from the list
> here:
> >
> > Suppose I have a long running transaction which has a few updates and
> > inserts running on some specific tables which means it has acquired
> > Exclusive locks too during the transaction on specific table but if
> just
> > before commit the client app crashes and the commit is never sent,
> will the
> > Exclusive locks be automatically released?
>
> Yes  (assuming the backend dies in the process, which may not happen if
> the app dies silently and while not waiting for anything from the
> server).
>
>
Do you mean that the Ex-lock will be held indefinitely in the following
situation"

i) Appln. acquires Exclusive lock.
ii) Appln. sleeps or is interacting with human.
ii) Appln. crashes.

Doesn't the backend kill itself if it detects that the other side of the
communincation channel has gone down?


--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | yahoo }.com

Re: [GENERAL] accidentally deleted user --> postgres

[Shoaib Mir]

This error actually indicates that the host and port information you are
providing for the PostgreSQL database server is incorrect or there is not
database server running on that (or it might be the firewall settings too).

First of all what I will recommend is to check on your database server to
see if the db server is running or not. You can do that using:

pg_ctl -D  status

Once that gives a good status then you might have to tweak 'pg_hba.conf'
file to allow for users connecting to the database.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/2/07, gf <[EMAIL PROTECTED]> wrote:



Hello all,
I was recently installing pg on a virtual machine. I also had pg installed
and working on my local machine.
On the vm I was having some issues installing a Drupal db so in searching
for a solution I found a recommendation of the following:
net user postgres /delete
and then reinstall pg.

I ran this command in what I thought was my newly built virtual machine's
dos window but it turned out to be the window for my localmachine which
had
a fully functioning pg install with several dbs.
I was able to get my virtual machine up and running with pg and drupal as
I
was easily able to reinstall pg.

The problem I have is I cannot connect to the postgres sql server database
using pgadmin III on my localmachine(the one I accidentally ran net user
postgres /delete on).

If I open up pgadmin and then in left column I have:
database
below this I have:
PostgreSQL Database Server 8.1(localhost:5432)...
If I right click on the above and select connect, I get the following
error:
Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused (0x274D/10061) Is the
server running on host "127.0.0.1" and accepting TCP/IP connections on
port
5432?


Any Ideas?
I am new to PG so perhaps it is a simple adduser statement(I hope)?
Thanks in advance.
--
View this message in context:
http://www.nabble.com/accidentally-deleted-user---%3E-postgres-tf3161276.html#a8768443
Sent from the PostgreSQL - general mailing list archive at Nabble.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] accidentally deleted user --> postgres

[Shane Ambler]

gf wrote:

Hello all,
I was recently installing pg on a virtual machine. I also had pg installed
and working on my local machine.
On the vm I was having some issues installing a Drupal db so in searching
for a solution I found a recommendation of the following:
net user postgres /delete
and then reinstall pg.


You will find that this is a standard system userid that was removed and 
is normally setup/created when installing postgresql. You will need to 
either reinstall postgresql on your local system or recreate the 
postgres user on your local system (not your vm)


Part of the security of postgresql is that the user running the server 
itself has limited privileges so that the database data is all that it 
can access. Without this user account it will not run, if you create the 
userid manually make sure it is not a member of the admin group and 
check that the postgresql service is started with that userid.



I ran this command in what I thought was my newly built virtual machine's
dos window but it turned out to be the window for my localmachine which had
a fully functioning pg install with several dbs.
I was able to get my virtual machine up and running with pg and drupal as I
was easily able to reinstall pg.

The problem I have is I cannot connect to the postgres sql server database
using pgadmin III on my localmachine(the one I accidentally ran net user
postgres /delete on).

If I open up pgadmin and then in left column I have:
database 
below this I have:

PostgreSQL Database Server 8.1(localhost:5432)...
If I right click on the above and select connect, I get the following error:
Server doesn't listen
The server doesn't accept connections: the connection library reports 
could not connect to server: Connection refused (0x274D/10061) Is the

server running on host "127.0.0.1" and accepting TCP/IP connections on port
5432?


Any Ideas?
I am new to PG so perhaps it is a simple adduser statement(I hope)?
Thanks in advance.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] Postgres SQL Syntax

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
"Jim C." <[EMAIL PROTECTED]> writes:

> Maybe it is and maybe it isn't.  I wouldn't know.  I'm merely the
> unfortunate soul chosen to convert this from MySQL to Postgres. :-/

> I've been working on it for a week now. I've got to say that it pains me
> to know that there is apparently no open standard in use for
> importing/exporting data from one db to another.

Regarding just the data, you could use "mysqldump --tab=DIR" on the
MySQL side and "COPY FROM" on the PostgreSQL side.

> XML would do the job, wouldn't it?

If you have a problem and try to solve it with XML, then you have two problems.


---(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] table updated status

[Richard Huxton]

finecur wrote:

Hi,

Can I list tables and the time they were last update (adding columns,
drop columns) using sql, something like the "ls -l" command under
unix?


No. There's no automatic timestamping. You could add your own triggers 
if you wanted though.



Can I compare the table definitions (tables, fields, but not data) of
two database using sql, > 

something like the "diff file1, file2" command

under unix?


You can use the standards-based information_schema views or the 
pg-specific system catalogue for this. See the manuals for details.


You might find it useful to start psql with -E, that way \dt and the 
like will show you what SQL they execute.


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

[GENERAL] Retrieving affected tables

[Hannes Dorbath]

What is best way to retrieve all affected tables of an select statement? 
(Besides parsing the raw SQL).


Thanks.


--
Regards,
Hannes Dorbath

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

  http://archives.postgresql.org/

[GENERAL] Storing database in cluster (Memory)

[roopa perumalraja]

Hi all,
   
  As I am inserting 100million rows daily into partitioned tables (daily wise), 
it is getting slower. Even the retrivel of data, select statement on those 
tables takes about 30 mintues. I have tried increasing the parameters in 
postgres.conf but still that doesn't help me much as the no of rows are huge. 
Will the idea of storing the database in cluster (memory) increase the 
performance of insert/update/select in the table in a suitation like this? 
Thanks a lot in advance.
   
  Regards
  Roopa

 
-
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.

Re: [GENERAL] Storing database in cluster (Memory)

[Matthias . Pitzl]

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of roopa perumalraja
Sent: Tuesday, February 06, 2007 12:33 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Storing database in cluster (Memory)


Hi all,
 
As I am inserting 100million rows daily into partitioned tables (daily
wise), it is getting slower. Even the retrivel of data, select statement on
those tables takes about 30 mintues. I have tried increasing the parameters
in postgres.conf but still that doesn't help me much as the no of rows are
huge. Will the idea of storing the database in cluster (memory) increase the
performance of insert/update/select in the table in a suitation like this?
Thanks a lot in advance.
 
Regards
Roopa



  _  

Don't pick lemons.
See all the new
 2007 cars at Yahoo!
 Autos. 
 

Hello Roopa,
 
Are you doing any vacuum runs on these tables? Most time degrading
performance one highly updated tables is caused by not performing any vacuum
runs.
 
Greetings,
Matthias

[GENERAL] Very strange error

[Ümit Öztosun]

Hi,

Today suddenly our PostgreSQL 8.1 server started producing strange errors.
Error occurs during simple updates:

"Table has type character varying, but query expects character varying."

We are still trying to figure out the problem. I've googled for this error
but found nothing. Any insight?

Platform: Ubuntu Dapper, Running PostgreSQL 8.1 (vanilla packages from
Ubuntu), UTF-8 and non-US locale.

Regards,
--
Ümit Öztosun

Re: [GENERAL] Retrieving affected tables

[Richard Huxton]

Hannes Dorbath wrote:
What is best way to retrieve all affected tables of an select statement? 
(Besides parsing the raw SQL).


From where? As a client-application function? As a user-callable 
function in the server? From within the parse/execute code?


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Problems With DELETE

[Jeanna Geier]

 Hello List-

 I'm having some problems in my program that I've been spending the day
going over and over and am now going to ask for some help, as I cannot see
the problem, but am hoping some seasoned sets of eyes will!

 I have some VIEWS that need to get updated when the user chooses to delete
data from them from within my program.  Most views are working, but it seems
that one is not.

 Here's one that IS working:

 CREATE OR REPLACE VIEW
 "elementdata"."personnel_info"
 AS
 SELECT
 pi.projectname, pi.people_id,
 pi.elementid, p.last_name, p.first_name,
 p.job_title, p.business_email, p.business_phone,
 p.cell_phone, p.pager_no
 FROM
 elementdata.data_personnel_info pi
 INNER JOIN projectdata.people p USING (projectname, people_id);


 CREATE RULE
 delete_personnel_info
 AS ON DELETE TO
 elementdata.personnel_info
 DO INSTEAD DELETE FROM
 elementdata.data_personnel_info
 WHERE
 data_personnel_info.elementid = old.elementid
 AND
 data_personnel_info.people_id::text = old.people_id::text;


 CREATE RULE
 update_personnel_info
 AS ON UPDATE TO
 elementdata.personnel_info
 DO INSTEAD

 (
 --update data_personnel_info
 UPDATE
 elementdata.data_personnel_info
 SET
 projectname = new.projectname,
 people_id = new.people_id
 WHERE
 ((data_personnel_info.elementid = old.elementid)
 AND
 ((data_personnel_info.people_id)::text = (old.people_id)::text));

 --update shown projectdata.people fields
 UPDATE
 projectdata.people
 SET
 last_name = new.last_name,
 first_name = new.first_name,
 job_title = new.job_title,
 business_email = new.business_email,
 business_phone = new.business_phone,
 cell_phone = new.cell_phone,
 pager_no = new.pager_no
 WHERE
 (((people.projectname)::text = (old.projectname)::text)
 AND
 ((people.people_id)::text = (old.people_id)::text));

 );


 And now, for my 'measurement view', which is NOT working; when the user
chooses delete from the program, it's not deleting it from the database.
And if I run the statement in pgAdmin: delete from elementdata.measurement
where elementid = 18700; (a row in the measurement table of my database) -
it's telling me that it did something: "Query returned successfully: 1 rows
affected, 703 ms execution time.", but if I query the table based on that
elementid (select * from elementdata.measurement where elementid = 18700;),
it's still in the table...

 Here are my CREATE, DELETE, and UPDATE statements for the 'measurement'
view:

 Create view "elementdata"."measurement" As
 SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
a.area * su.units_per_sqfoot AS area_sq, e.slope AS slope_inches,
sa.slopearea * cu.units_per_cufoot AS slopearea_sq, da.linear_unit AS
linear_unit_lin, (p.perimeter * lu.units_per_foot::double precision) AS
perimeter_lin, da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot
AS height_lin, e.height::double precision * a.area * cu.units_per_cufoot AS
volume_cu, da.drawingid
   FROM
 ((SELECT perimeter.elementid, perimeter.perimeter
 FROM elementdata.perimeter
 UNION
 SELECT length.elementid, length.length AS perimeter
   FROM elementdata.length)
 UNION
 SELECT circumference.elementid, circumference.circumference AS perimeter
   FROM elementdata.circumference) p
 LEFT JOIN elementdata.area a USING (elementid))
 LEFT JOIN element e USING (elementid))
 LEFT JOIN elementdata.slopearea sa USING (elementid))
 JOIN layer la USING (layerid))
 JOIN drawing da USING (drawingid))
 JOIN globaldata.linear_units lu USING (linear_unit))
 JOIN globaldata.square_units su USING (square_unit))
 JOIN globaldata.cubic_units cu USING (cubic_unit));>


 CREATE OR REPLACE RULE
 delete_measurement
 AS ON DELETE TO
 elementdata.measurement
 DO INSTEAD DELETE FROM
 elementdata.data_measurement
 WHERE
 ((data_measurement.elementid = old.elementid)
 AND
 (element.elementid = old.elementid));


 CREATE RULE update_measurement AS ON UPDATE
TO elementdata.measurement
DO INSTEAD (UPDATE element SET height = (new.height_lin / (SELECT
linear_units.units_per_foot FROM globaldata.linear_units WHERE
((linear_units.linear_unit)::text = (old.linear_unit_lin)::text))), slope =
new.slope_inches WHERE (element.elementid = old.elementid); UPDATE drawing
SET linear_unit = new.linear_unit_lin, square_unit = new.square_unit_sq,
cubic_unit = new.cubic_unit_cu WHERE (drawing.drawingid = old.drawingid); );



 Any thoughts/ideas would be greatly appreciated - I'm stuck as to why it's
working on all the views in the schema, but one...  If you need anymore info
from me, just let me know.

 Thanks in advance for your input and ideas!!
 -Jeanna


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

Re: [GENERAL] Very strange error

[Matthias . Pitzl]

> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ümit Öztosun
> Sent: Tuesday, February 06, 2007 2:50 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Very strange error
> 
> 
> Hi,
> 
> Today suddenly our PostgreSQL 8.1 server started producing strange errors.
Error occurs during simple updates:
> 
> "Table has type character varying, but query expects character varying." 
> 
> We are still trying to figure out the problem. I've googled for this error
but found nothing. Any insight?
> 
> Platform: Ubuntu Dapper, Running PostgreSQL 8.1 (vanilla packages from
Ubuntu), UTF-8 and non-US locale. 
> 
> Regards,
> -- 
> Ümit Öztosun

 
Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.

Greetings,
Matthias 

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

Re: [GENERAL] Storing database in cluster (Memory)

[Richard Huxton]

roopa perumalraja wrote:

Hi all,

As I am inserting 100million rows daily into partitioned tables
(daily wise), it is getting slower. 


What is - the inserts? By how much? What tables? What indexes? How are 
you inserting these rows?


> Even the retrivel of data, select
statement on those tables takes about 30 mintues. 


All selects take 30 minutes, regardless what work they do? Or do you 
have specific selects that are causing problems?


> I have tried

increasing the parameters in postgres.conf but still that doesn't
help me much as the no of rows are huge. 


What parameters, to what values? What hardware are you running on? What 
load do you place on it?


> Will the idea of storing the

database in cluster (memory) increase the performance of
insert/update/select in the table in a suitation like this? Thanks a
lot in advance.


A better question would be "Why isn't my data being cached?". The 
operating-system should cache regularly used files.


Still - more details please roopa and we'll see if anyone can help you.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Retrieving affected tables

[Hannes Dorbath]

On 06.02.2007 14:19, Richard Huxton wrote:
What is best way to retrieve all affected tables of an select 
statement? (Besides parsing the raw SQL).


 From where? As a client-application function? As a user-callable 
function in the server? From within the parse/execute code?


From a client application.

--
Regards,
Hannes Dorbath

---(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] Retrieving affected tables

[Richard Huxton]

Hannes Dorbath wrote:

On 06.02.2007 14:19, Richard Huxton wrote:
What is best way to retrieve all affected tables of an select 
statement? (Besides parsing the raw SQL).


 From where? As a client-application function? As a user-callable 
function in the server? From within the parse/execute code?


 From a client application.


Parsing the sql is the only way I can think of.

You could feed it through EXPLAIN I suppose, but there's no way to dump 
the query-plan as a data-structure to the client. You can log a verbose 
query-plan, but that's not going to help you.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] PostgreSQL/FireBird

[Brandon Aiken]

Borland simply chose a modified MPL to release their InterBase 6 under.
They have since release InterBase 6 under a commercial license, and have
also released InterBase 7 under a commercial license.  MPL is a fairly
common license.  Sun's CDDL is a modified MPL, for example.  The MPL is
somewhere between a BSD license and the GPL in terms of what you can do
with it.  Unlike BSD, all code changes must stay under the MPL.  Unlike
the GPL, MPL code can be combined with proprietary files.  MySQL's
license is a lot more complicated than the MPL.

 

The FSF says the MPL is not compatible with the GPL, but, well, the FSF
generally finds *all* non-GPL licenses incompatible with the GPL (BSD,
MPL, Apache, etc.).  The only truly GPL-compatible license I know of is
LGPL (and there have been arguments about that).  That's the problem
with the GPL.  You're not agreeing to open source your code as much as
you're agreeing to abide by the FSF's political beliefs.  Political
lock-in for developers in lieu of vendor lock-in for end-users.

 

Compared to SQLite, Firebird has many more features.  Firebird *can*
function as a network server and runs as a separate process instead of a
C library that gets compiled in your binary.  If you want multiple apps
to access the same data or you want to use ODBC, Firebird can do that
without the kitchen sink approach of PostgreSQL.

 

Compared to JetSQL - which I assume is what Access and Exchange use -
Firebird is cross-platform.  I've never used it, but I've also never
been impressed with the performance of anything that has used JetSQL
(Exchange especially).

 

--

Brandon Aiken

CS/IT Systems Engineer



From: Justin Dearing [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 05, 2007 6:29 PM
To: Brandon Aiken
Subject: Re: [GENERAL] PostgreSQL/FireBird

 

 

On 2/5/07, Brandon Aiken <[EMAIL PROTECTED]> wrote:

FireBird is a direct descendant of Borland InterBase 6.  Consequently,
much like Postgres inherited a lot of Ingres's weirdness (most of which
has since been weeded out or superceeded with standard SQL compliance), 
FireBird is still very much InterBase dialect-compliant.  This is also
why it still uses a modified Mozilla Public License.  I know they've
achieved ANSI SQL-92 compliance, but I don't know how fully compliant 
beyond that they are.  PostgreSQL is mostly working on SQL-03 compliance
AFAICT.  Both use MVCC.


What does the MPL have to do with Borland InterBase descendance? Borland
could have chosen any license they wished. Quite frankly I'm quite
ignorant about the MPLs terms so please enlighten me. 

 

Interbase was also primarily used for single instance and
embedded
applications, so it's not intended to scale the same way
PostgreSQL is. 


 So I guess one should ask how it scales to SQLite and JetSQL, on the
appropiate lists of course.

 

Firebird's design foci are very small memory footprint, ANSI
SQL-92
complaince, multiple dialects that support aging systems, and
very low
administrative requirements.  It lack features and scalability
compares
to PG, but does what it does very well.

 

Bottom line:  PostgreSQL is more mature because it's several
years 
older.  Firebird is intended for different applications.

 

If FireBird is descended from Ingres, aren't they both the same age?




** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

Re: [GENERAL] Postgres SQL Syntax

[Brandon Aiken]

This is one instance where I think PGAdmin would really help.  You know
what the schema needs to be, yes?  Create it will PGAdmin and you can
see what some well-formatted PG code looks like.

The majority of the differences in syntax between MySQL and PG are
*generally* MySQL's fault.  MySQL has more unique extensions and
shortcuts for SQL than any other RDBMS I know of.

Keep in mind, though, that no database is 100% ANSI SQL only.  Indeed, I
don't know of any database that is 100% ANSI SQL compliant.  The
relational model does not translate into computer data very well, and
the requirements to implement it contradict some pretty basic computer
restrictions.  At several points along the way, the demands of the
theoretical relational model break down and practical applications are
used instead.  The limitations of modern computers make a true RDB as
envisioned by Boyd and Cobb a virtual impossibility (the most obvious
reason being that a computer database can only store computerized data).

--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jim C.
Sent: Friday, February 02, 2007 11:37 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres SQL Syntax

> CREATE TABLE "credits" (
>   "person" integer NOT NULL default '0',
>   "chanid" int NOT NULL default '0',
>   "starttime" timestamp NOT NULL default '1970-01-01 00:00:00+00',
>   "role"
set('actor','director','producer','executive_producer','writer','guest_s
tar','host','adapter','presenter','commentator','guest') NOT NULL
default ''
> --  CONSTRAINT "chanid_constraint0" UNIQUE
("chanid","starttime","person","role")
> --  UNIQUE KEY "chanid" ("chanid","starttime","person","role"),
> --  KEY "person" ("person","role")
> );

I'm doing this table by table, line by line.  Each table, I learn
something new about the differences between MySQL and Postgres, I
mentally catalog it and I can always look it up in my own code next time
for examples.

I've a tool that is providing some help but sometimes it chokes. It
choked on this one for example.

I could use some clues as to how to go about converting this MySQL
implementation of roles to Postgres.

So far I've been through 5 tables and it is getting easier but I'm still
getting stuck now and then.


Jim C.


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



** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

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

Re: [GENERAL] Very strange error

[Ümit Öztosun]

Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning
a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.



Well, I've just  dumped old data, installed  v8.2.2 from sources,  restored
data. Unfortunately the error remains the same and we have no ideas left.
Error is again:

"Table has type character varying, but query expects character varying."

The error is about a varchar column, with no other special attributed. It
was working flawlessly for a long time.

Any help is appreciated.

Regards,
Ümit Öztosun

Re: [GENERAL] Very strange error

[Michael Slattery]

When does this error crop up?  What is the query?  Does this select  
involve more than one table, or does it involve any homemade  
functions?  Or overriden functions?



On Feb 6, 2007, at 9:58 AM, Ümit Öztosun wrote:



Have you installed any updates for PostgreSQL? The latest security  
update

fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list  
concerning a

broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.

Well, I've just  dumped old data, installed  v8.2.2 from sources,   
restored data. Unfortunately the error remains the same and we have  
no ideas left. Error is again:


"Table has type character varying, but query expects character  
varying."


The error is about a varchar column, with no other special  
attributed. It was working flawlessly for a long time.


Any help is appreciated.

Regards,
Ümit Öztosun




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

Re: [GENERAL] Retrieving affected tables

[Hannes Dorbath]

On 06.02.2007 15:00, Richard Huxton wrote:

Hannes Dorbath wrote:

On 06.02.2007 14:19, Richard Huxton wrote:
What is best way to retrieve all affected tables of an select 
statement? (Besides parsing the raw SQL).


 From where? As a client-application function? As a user-callable 
function in the server? From within the parse/execute code?


 From a client application.


Parsing the sql is the only way I can think of.

You could feed it through EXPLAIN I suppose, but there's no way to dump 
the query-plan as a data-structure to the client. You can log a verbose 
query-plan, but that's not going to help you.


EXPLAIN VERBOSE gives me :resorigtbl. I think that is what I was after.


--
Regards,
Hannes Dorbath

---(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] Very strange error

[Matthias . Pitzl]

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ümit Öztosun
Sent: Tuesday, February 06, 2007 3:59 PM
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Very strange error





Have you installed any updates for PostgreSQL? The latest security update 
fixed something with type checks or so. 
I've seen the same error message also on the BUGS mailing list concerning a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.


Well, I've just  dumped old data, installed  v8.2.2 from sources,  restored
data. Unfortunately the error remains the same and we have no ideas left.
Error is again:

"Table has type character varying, but query expects character varying." 

The error is about a varchar column, with no other special attributed. It
was working flawlessly for a long time.


Any help is appreciated.

Regards,
Ümit Öztosun
 

Hello there!
 
I suggest to post this on the BUGS mailing list. As said before, there has
been some other mail with exact the same error message and with the latest
version something concerning data type checks had been fixed.
 
Greetings,
Matthias

[GENERAL] create operator class problem

[Ron Peterson]

I wanted to play with ltree, but I'm having trouble running ltree.sql.
Everything in ltree.sql seems to work, except for the CREATE OPERATOR
CLASS statements, which error out like:

ERROR:  syntax error at or near "OPERATOR10"
LINE 3: OPERATOR10<@ (_ltree, ltree)RECHECK ,

...which is generated from:

CREATE OPERATOR CLASS gist__ltree_ops
DEFAULT FOR TYPE _ltree USING gist AS
OPERATOR10  <@ (_ltree, ltree)  RECHECK ,
OPERATOR11  @> (ltree, _ltree)  RECHECK ,
OPERATOR12  ~ (_ltree, lquery)  RECHECK ,
OPERATOR13  ~ (lquery, _ltree)  RECHECK ,
OPERATOR14  @ (_ltree, ltxtquery)   RECHECK ,
OPERATOR15  @ (ltxtquery, _ltree)   RECHECK ,
OPERATOR16  ? (_ltree, _lquery) RECHECK ,
OPERATOR17  ? (_lquery, _ltree) RECHECK ,
FUNCTION1   _ltree_consistent (internal, internal, int2),
FUNCTION2   _ltree_union (internal, internal),
FUNCTION3   _ltree_compress (internal),
FUNCTION4   ltree_decompress (internal),
FUNCTION5   _ltree_penalty (internal, internal, internal),
FUNCTION6   _ltree_picksplit (internal, internal),
FUNCTION7   _ltree_same (internal, internal, internal),
STORAGE ltree_gist;

I'm running PostgreSQL 8.2.1.

I also gave the 'seg' contrib module a whirl, and it also errors out on
the CREATE OPERATOR CLASS statement.

-- 
Ron Peterson
https://www.yellowbank.com/

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

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

Re: [GENERAL] Very strange error

[Ümit Öztosun]

Hello there!

I suggest to post this on the BUGS mailing list. As said before, there has
been some other mail with exact the same error message and with the latest
version something concerning data type checks had been fixed.

Greetings,
Matthias




I'm writing a seperate e-mail to the pgsql-bugs mailing list. Just in case
here is more detailed info:

  Tablo "
public.scf_fatura"
   Column   |Data Type
|  Modifiers
-+--+
_key| bigint   | not null default 0
_serial | integer  | not null default
nextval('scf_fatura__serial_seq'::regclass)
_rep| character(1) | not null default
'n'::bpchar
_user   | bigint   | default 0
_date   | timestamp with time zone |
_site   | smallint | default 0
turu| smallint | default 0
fisno   | character varying(50)| default
''::character varying
tarih   | date |
saat| time without time zone   |
belgeno | character varying(50)| default
''::character varying
belgeno2| character varying(50)| default
''::character varying
_key_scf_irsaliye   | bigint   | default 0
_key_sis_ozelkod1   | bigint   | default 0
_key_sis_ozelkod2   | bigint   | default 0
_key_sis_seviyekodu | bigint   | default 0
_key_scf_satiselemani   | bigint   | default 0
_key_sis_sube_source| bigint   | default 0
_key_sis_depo_source| bigint   | default 0
karsifirma  | character(1) | default ''::bpchar
_key_karsi_fatura   | bigint   | default 0
_key_scf_carikart   | bigint   | default 0
_key_scf_kasa   | bigint   | default 0
kasafisno   | character varying(16)| default
''::character varying
sevkadresi1 | character varying(128)   | default
''::character varying
sevkadresi2 | character varying(128)   | default
''::character varying
sevkadresi3 | character varying(128)   | default
''::character varying
_key_sis_firma_dest | bigint   | default 0
_key_sis_sube_dest  | bigint   | default 0
_key_sis_depo_dest  | bigint   | default 0
_key_sis_doviz  | bigint   | default 0
dovizkuru   | numeric(15,10)   | default 0.0
aciklama1   | character varying(128)   | default
''::character varying
aciklama2   | character varying(128)   | default
''::character varying
aciklama3   | character varying(128)   | default
''::character varying
toplammasraf| numeric(20,10)   | default 0.0
toplamindirim   | numeric(20,10)   | default 0.0
toplam  | numeric(20,10)   | default 0.0

 toplamotv   | numeric(20,10)   | default 0.0
toplamkdv   | numeric(20,10)   | default 0.0
net | numeric(20,10)   | default 0.0
toplammasrafdvz | numeric(20,10)   | default 0.0
toplamindirimdvz| numeric(20,10)   | default 0.0
toplamdvz   | numeric(20,10)   | default 0.0
toplamotvdvz| numeric(20,10)   | default 0.0
toplamkdvdvz| numeric(20,10)   | default 0.0
netdvz  | numeric(20,10)   | default 0.0
iptal   | character(1) | default
'-'::bpchar
kilitli | character(1) | default ''::bpchar
kdvduzenorani   | character(1) | default
'+'::bpchar
kdvduzentutari  | numeric(10,5)| default 0.0
_key_scf_malzeme_baglantisi | bigint   | default 0
_key_scf_odeme_plani| bigint   | default 0
_owner  | bigint   | default 0
_key_sis_doviz_raporlama| bigint   | default 0::bigint
raporlamadovizkuru  | numeric(9,5) | default 1
ekmaliyet   | numeric(16,7)| default 0.0
_key_muh_masrafmerkezi  | bigint   | default 0
ortalamavade| date |
Indexes:
   "scf_fatura_pkey" PRIMARY KEY, btree (_key)

[GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Thomas F. O'Connell]

I'm working on a system where postgres 8.2.1 was built from source on  
Solaris 10 using gcc. Based on a number of recommendations, we  
decided to rebuild postgres Sun Studio cc. Without changing  
platforms, I wouldn't've expected the compiler to make a difference,  
but we just built 8.2.2 from source using cc, and now we're seeing  
this type of error in the logs:


ERROR:  attribute 3 has wrong type
DETAIL:  Table has type character varying, but query expects  
character varying.


Is changing compilers under postgres on the same platform without a  
dump/reload a Bad Idea?


More important: Has this risked any catastrophic data corruption? If  
we just switch to a gcc 8.2.2, will we be fine?


--
Thomas F. O'Connell

optimizing modern web applications
: for search engines, for usability, and for performance :

http://o.ptimized.com/
615-260-0005

Re: [GENERAL] create operator class problem

[Tom Lane]

Ron Peterson <[EMAIL PROTECTED]> writes:
> I wanted to play with ltree, but I'm having trouble running ltree.sql.
> Everything in ltree.sql seems to work, except for the CREATE OPERATOR
> CLASS statements, which error out like:

> ERROR:  syntax error at or near "OPERATOR10"
> LINE 3: OPERATOR10<@ (_ltree, ltree)RECHECK ,

Looks like something removed the tabs in that line?

regards, tom lane

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

Re: [GENERAL] Help compile pgmemcache against PG 8.2

[Chander Ganesan]

Richard Huxton wrote:

Hannes Dorbath wrote:

http://pgfoundry.org/projects/pgmemcache/

We'd like to use pgmemcache with PG 8.2. The memcached version 
doesn't matter much (1.2.x is current, 1.1.x would be fine as well).


It fails to build against 8.2 with various errors. The project seems 
unmaintained and I failed to get any answer from the author. I'm no C 
developer but with some guess work and google I was able to fix a few 
things, but not all.


Hmm - Sean Chittenden. He was around in the summer according to the 
archives. Nothing more recent than that. Sean - you around at present?


If anyone more capable in C could wast a few minutes of his/her time 
and look at it, we'd really appreciate it. If it's nothing that can 
be fixed in a few minutes, don't wast your time, we'll look for 
something else.


Anyone here able to help Hannes?

We (Open Technology Group, Inc.) are now sponsoring development of 
pgmemcache.  Version 1.1beta is on pgfoundry and 1.1 will be released 
shortly.  There is also a version 1.2 on the horizon as well...which 
should provide some performance improvements as well.


The Open Technology Group, Inc. also provides training - our PostgreSQL 
Performance & Tuning course covers the installation and use of 
pgmemcache, as well as numerous other performance related topics. 


http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47

--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com


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

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Andrew Sullivan]

On Tue, Feb 06, 2007 at 09:43:01AM -0600, Thomas F. O'Connell wrote:
> DETAIL:  Table has type character varying, but query expects  
> character varying.

In another thread, someone else is reporting this too.  I'm
wondering whether something went wrong in the 8.2.2 release.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

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

[Walter Vaughan]

I need to purchase a new server to put posgresql on that will be acting as the 
DBMS server for Apache ofBiz soon. While googling around for performance tweaks 
I saw this at http://revsys.com/writings/postgresql-performance.html



CPUs — The more CPUs the better, however if your database does not use many 
complex functions your money is best spent on a better disk subsystem. Also, 
avoid Intel Xeon processors with PostgreSQL as there is a problem with the 
context switching in these processors that gives sub-par performance. Opterons 
are generally accepted as being a superior CPU for PostgreSQL databases.



Is this still true in regards to Xeon's? I was looking at a server with Quad 
Core Xeon 2 5335 @ 2.0GHz.


And at http://www.powerpostgresql.com/PerfList


focus on RAID 1 or 1+0 or 0+1 for any set of 2, 4 or 6 disks.


Are RAID 1 or 1+0 or 0+1 equal in speed, performance, downtime in regards to 
postgresql. Is it a coin toss?


---(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] Postgres SQL Syntax

[Richard Huxton]

Jim C. wrote:


I'm doing this table by table, line by line.  Each table, I learn
something new about the differences between MySQL and Postgres, I
mentally catalog it and I can always look it up in my own code next time
for examples.

I've a tool that is providing some help but sometimes it chokes. It
choked on this one for example.

I could use some clues as to how to go about converting this MySQL
implementation of roles to Postgres.

So far I've been through 5 tables and it is getting easier but I'm still
getting stuck now and then.


You probably want one of the mysql converter projects, e.g.
  http://pgfoundry.org/projects/mysql2pgsql/
Also read the "converting from other databases" section here:
  http://www.postgresql.org/docs/techdocs

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Thomas F. O'Connell]

On Tue, Feb 06, 2007 at 09:43:01AM -0600, Thomas F. O'Connell wrote:
> DETAIL:  Table has type character varying, but query expects
> character varying.

In another thread, someone else is reporting this too.  I'm
wondering whether something went wrong in the 8.2.2 release.


Is this the other thread?

http://archives.postgresql.org/pgsql-general/2007-02/msg00235.php

This looks like it's affecting 8.1...

--
Thomas F. O'Connell

optimizing modern web applications
: for search engines, for usability, and for performance :

http://o.ptimized.com/
615-260-0005

Re: [GENERAL] create operator class problem

[Ron Peterson]

On Tue, Feb 06, 2007 at 10:45:23AM -0500, Tom Lane wrote:
> Ron Peterson <[EMAIL PROTECTED]> writes:
> > I wanted to play with ltree, but I'm having trouble running ltree.sql.
> > Everything in ltree.sql seems to work, except for the CREATE OPERATOR
> > CLASS statements, which error out like:
> 
> > ERROR:  syntax error at or near "OPERATOR10"
> > LINE 3: OPERATOR10<@ (_ltree, ltree)RECHECK ,
> 
> Looks like something removed the tabs in that line?

Ah, yep.  For some reason the emacs 'sql-send-region' command in
sql-postgres mode is munging that line.  If I just read the file
directly everything is fine.  Thanks.

-- 
Ron Peterson
https://www.yellowbank.com/

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

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Jim Nasby]

On Feb 6, 2007, at 9:54 AM, Andrew Sullivan wrote:

On Tue, Feb 06, 2007 at 09:43:01AM -0600, Thomas F. O'Connell wrote:

DETAIL:  Table has type character varying, but query expects
character varying.


In another thread, someone else is reporting this too.  I'm
wondering whether something went wrong in the 8.2.2 release.


The error message originates in backend/executor/ 
nodeFunctionscan.c:tupledesc_match. In this case the error is being  
caused by having a functional index (lower(fieldname)) on a table.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

Re: [GENERAL] Postgres SQL Syntax

[Jim C.]

> You probably want one of the mysql converter projects, e.g.
>   http://pgfoundry.org/projects/mysql2pgsql/
> Also read the "converting from other databases" section here:
>   http://www.postgresql.org/docs/techdocs

I tried several conversion tools and did get some minor success with one
or two but mostly they didn't work. The article at this site seems good
at first blush but then one notices that it doesn't seem to have been
updated any time in the last five years.


Jim C.




signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Very strange error

[Ümit Öztosun]

Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning
a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.



Well, I've just  dumped old data, installed  v8.2.2 from sources,  restored
data. Unfortunately the error remains the same and we have no ideas left.
Error is again:

"Table has type character varying, but query expects character varying."

The error is about a varchar column, with no other special attributed. It
was working flawlessly for a long time.

Any help is appreciated.

Regards,
Ümit Öztosun

Re: [GENERAL] Storing database in cluster (Memory)

[Hiltibidal, Robert]

You might take a look at index anding for speeding up your selects

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton
Sent: Tuesday, February 06, 2007 7:24 AM
To: roopa perumalraja
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Storing database in cluster (Memory)

roopa perumalraja wrote:
> Hi all,
> 
> As I am inserting 100million rows daily into partitioned tables
> (daily wise), it is getting slower. 

What is - the inserts? By how much? What tables? What indexes? How are 
you inserting these rows?

 > Even the retrivel of data, select
> statement on those tables takes about 30 mintues. 

All selects take 30 minutes, regardless what work they do? Or do you 
have specific selects that are causing problems?

 > I have tried
> increasing the parameters in postgres.conf but still that doesn't
> help me much as the no of rows are huge. 

What parameters, to what values? What hardware are you running on? What 
load do you place on it?

 > Will the idea of storing the
> database in cluster (memory) increase the performance of
> insert/update/select in the table in a suitation like this? Thanks a
> lot in advance.

A better question would be "Why isn't my data being cached?". The 
operating-system should cache regularly used files.

Still - more details please roopa and we'll see if anyone can help you.

-- 
   Richard Huxton
   Archonet Ltd

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


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.





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

Re: [GENERAL] Hardware

[Andrew Sullivan]

On Tue, Feb 06, 2007 at 10:59:21AM -0500, Walter Vaughan wrote:
> 
> Is this still true in regards to Xeon's? I was looking at a server with 
> Quad Core Xeon 2 5335 @ 2.0GHz.

Multi-core Xeons are not as affected, and are somewhat different
"under the hood".  So no, you're probably ok there. 

> Are RAID 1 or 1+0 or 0+1 equal in speed, performance, downtime in regards 
> to postgresql. Is it a coin toss?

Well, 1 isn't equivalent to 1+0 or 0+1 in terms of capacity, because
it's a straight mirror of two drives.  I hate 0+1, because you lose
half the array in the event any disk in the side fails.  So I always
use 1+0 if I can.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner

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

[Guido Neitzer]

On 06.02.2007, at 08:59, Walter Vaughan wrote:

Is this still true in regards to Xeon's? I was looking at a server  
with Quad Core Xeon 2 5335 @ 2.0GHz.


No, it's not true anymore. See

http://tweakers.net/reviews/657/1

for an interesting comparison.

cug

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

Re: [GENERAL] replication choices

[Andrew Sullivan]

On Thu, Jan 25, 2007 at 12:17:52PM -0800, Ben wrote:
> familiar with Slony, and from what I understand, using Slony with bad 
> networks leads to bad problems. I'm also not sure that Slony supports 
> replicating from multiple sources to the same postgres install, even if 
> each replication process is writing to a different schema.

Yes, you can have multiple origins into the same database, without a
problem.  I'd be worried for sure about the network unreliability,
though.  You might, however, be able to do this usefully using the
log shipping features of Slony.

I would _not_ worry about the outbound replication from the centre,
assuming that the changes are infrequent.  

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
--Brad Holland

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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Tom Lane]

"Thomas F. O'Connell" <[EMAIL PROTECTED]> writes:
> but we just built 8.2.2 from source using cc, and now we're seeing  
> this type of error in the logs:

> ERROR:  attribute 3 has wrong type
> DETAIL:  Table has type character varying, but query expects  
> character varying.

This has nothing to do with your compiler :-(

regards, tom lane

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

Re: [GENERAL] Help compile pgmemcache against PG 8.2

[Richard Huxton]

Chander Ganesan wrote:

Richard Huxton wrote:

Hannes Dorbath wrote:

http://pgfoundry.org/projects/pgmemcache/

We'd like to use pgmemcache with PG 8.2. The memcached version 
doesn't matter much (1.2.x is current, 1.1.x would be fine as well).


It fails to build against 8.2 with various errors. The project seems 
unmaintained and I failed to get any answer from the author. I'm no C 
developer but with some guess work and google I was able to fix a few 
things, but not all.


Hmm - Sean Chittenden. He was around in the summer according to the 
archives. Nothing more recent than that. Sean - you around at present?


If anyone more capable in C could wast a few minutes of his/her time 
and look at it, we'd really appreciate it. If it's nothing that can 
be fixed in a few minutes, don't wast your time, we'll look for 
something else.


Anyone here able to help Hannes?

We (Open Technology Group, Inc.) are now sponsoring development of 
pgmemcache.  Version 1.1beta is on pgfoundry and 1.1 will be released 
shortly.  There is also a version 1.2 on the horizon as well...which 
should provide some performance improvements as well.


Excellent. Well done OTG.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Help compile pgmemcache against PG 8.2

[Hannes Dorbath]

On 06.02.2007 17:04, Richard Huxton wrote:

Chander Ganesan wrote:

Richard Huxton wrote:

Hannes Dorbath wrote:

http://pgfoundry.org/projects/pgmemcache/

We'd like to use pgmemcache with PG 8.2. The memcached version 
doesn't matter much (1.2.x is current, 1.1.x would be fine as well).


It fails to build against 8.2 with various errors. The project seems 
unmaintained and I failed to get any answer from the author. I'm no 
C developer but with some guess work and google I was able to fix a 
few things, but not all.


Hmm - Sean Chittenden. He was around in the summer according to the 
archives. Nothing more recent than that. Sean - you around at present?


If anyone more capable in C could wast a few minutes of his/her time 
and look at it, we'd really appreciate it. If it's nothing that can 
be fixed in a few minutes, don't wast your time, we'll look for 
something else.


Anyone here able to help Hannes?

We (Open Technology Group, Inc.) are now sponsoring development of 
pgmemcache.  Version 1.1beta is on pgfoundry and 1.1 will be released 
shortly.  There is also a version 1.2 on the horizon as well...which 
should provide some performance improvements as well.


Excellent. Well done OTG.


That is great news, thanks!


--
Regards,
Hannes Dorbath

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

Re: [GENERAL] replication choices

[Andrew Sullivan]

On Wed, Jan 31, 2007 at 03:17:40PM -0800, Ben wrote:
> the remote sites back to the central site, each remote site needs to have 
> a normal slony node first, which I don't have the hardware for.

An answer for this, though a dirty kludge, is to replicate to another
database in the same cluster.  This is really no more load than the
single replication user, although it is expensive at the disk level.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL/FireBird

[Tim Tassonis]

The FSF says the MPL is not compatible with the GPL, but, well, the FSF 
generally finds **all** non-GPL licenses incompatible with the GPL (BSD, 
MPL, Apache, etc.).  The only truly GPL-compatible license I know of is 
LGPL (and there have been arguments about that).  That’s the problem 
with the GPL.  You’re not agreeing to open source your code as much as 
you’re agreeing to abide by the FSF’s political beliefs.  Political 
lock-in for developers in lieu of vendor lock-in for end-users.


Quite political stuff in a technical posting from somebody apparently 
disliking mixing politics and open source, eh?


It's been said a million times by BSD advocats: put one line of code 
under GPL and you instantly become a willingless slave of Richard 
Stallmans hoards of children-eating communists.


You don't seem to have the slightest idea of how little power the FSF 
even has over project initially developed by themselves (gcc etc), let 
alone stuff like the linux kernel.


But you still eternally repeat the "one license to bind them all" 
conspiracy theory.


Maybe it's time to move to some other lecture. How about "The 
Silmarillion", it's quite good as well.




 

Compared to SQLite, Firebird has many more features.  Firebird **can** 
function as a network server and runs as a separate process instead of a 
C library that gets compiled in your binary.  If you want multiple apps 
to access the same data or you want to use ODBC, Firebird can do that 
without the kitchen sink approach of PostgreSQL.



SQLite also has support for ODBC:

http://www.ch-werner.de/sqliteodbc/




 

Compared to JetSQL – which I assume is what Access and Exchange use – 
Firebird is cross-platform.  I’ve never used it, but I’ve also never 
been impressed with the performance of anything that has used JetSQL 
(Exchange especially).


 


--

Brandon Aiken

CS/IT Systems Engineer



*From:* Justin Dearing [mailto:[EMAIL PROTECTED]
*Sent:* Monday, February 05, 2007 6:29 PM
*To:* Brandon Aiken
*Subject:* Re: [GENERAL] PostgreSQL/FireBird

 

 

On 2/5/07, *Brandon Aiken* <[EMAIL PROTECTED] 
> wrote:


FireBird is a direct descendant of Borland InterBase 6.  Consequently,
much like Postgres inherited a lot of Ingres's weirdness (most of which
has since been weeded out or superceeded with standard SQL compliance),
FireBird is still very much InterBase dialect-compliant.  This is also
why it still uses a modified Mozilla Public License.  I know they've
achieved ANSI SQL-92 compliance, but I don't know how fully compliant
beyond that they are.  PostgreSQL is mostly working on SQL-03 compliance
AFAICT.  Both use MVCC.


What does the MPL have to do with Borland InterBase descendance? Borland 
could have chosen any license they wished. Quite frankly I'm quite 
ignorant about the MPLs terms so please enlighten me.


 


Interbase was also primarily used for single instance and embedded
applications, so it's not intended to scale the same way PostgreSQL is.


 So I guess one should ask how it scales to SQLite and JetSQL, on the 
appropiate lists of course.


 


Firebird's design foci are very small memory footprint, ANSI SQL-92
complaince, multiple dialects that support aging systems, and very low
administrative requirements.  It lack features and scalability compares
to PG, but does what it does very well.

 


Bottom line:  PostgreSQL is more mature because it's several years
older.  Firebird is intended for different applications.

 


If FireBird is descended from Ingres, aren't they both the same age?

 




** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.


This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of this 
message to the intended recipient(s), you are hereby notified that any 
dissemination, distribution or copying of this e-mail message is 
strictly prohibited. If you have received this message in error, please 
immediately notify the sender and delete this e-mail message from your 
computer.



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

Re: [GENERAL] PostgreSQL/FireBird

[Scott Marlowe]

On Tue, 2007-02-06 at 10:19, Tim Tassonis wrote:
> > The FSF says the MPL is not compatible with the GPL, but, well, the FSF 
> > generally finds **all** non-GPL licenses incompatible with the GPL (BSD, 
> > MPL, Apache, etc.).  The only truly GPL-compatible license I know of is 
> > LGPL (and there have been arguments about that).  That’s the problem 
> > with the GPL.  You’re not agreeing to open source your code as much as 
> > you’re agreeing to abide by the FSF’s political beliefs.  Political 
> > lock-in for developers in lieu of vendor lock-in for end-users.
> 
> Quite political stuff in a technical posting from somebody apparently 
> disliking mixing politics and open source, eh?
> 
> It's been said a million times by BSD advocats: put one line of code 
> under GPL and you instantly become a willingless slave of Richard 
> Stallmans hoards of children-eating communists.

That's ridiculous.  Everybody knows that they're more socialists at
heart.  

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

Re: [GENERAL] Hardware

[Lars Heidieker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On 6 Feb 2007, at 15:59, Walter Vaughan wrote:

I need to purchase a new server to put posgresql on that will be  
acting as the DBMS server for Apache ofBiz soon. While googling  
around for performance tweaks I saw this at http://revsys.com/ 
writings/postgresql-performance.html



CPUs — The more CPUs the better, however if your database does not  
use many complex functions your money is best spent on a better  
disk subsystem. Also, avoid Intel Xeon processors with PostgreSQL  
as there is a problem with the context switching in these  
processors that gives sub-par performance. Opterons are generally  
accepted as being a superior CPU for PostgreSQL databases.



Is this still true in regards to Xeon's? I was looking at a server  
with Quad Core Xeon 2 5335 @ 2.0GHz.


And at http://www.powerpostgresql.com/PerfList



It seems to be outdated. As far as I know it was supposed to warn for  
the HyperThreading things.


- --

Viele Grüße,
Lars Heidieker

[EMAIL PROTECTED]
http://paradoxon.info

- 

Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
 -- Friedrich Nietzsche



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)

iD8DBQFFyK36cxuYqjT7GRYRAmHfAJ0Z9q/a2m4gFreX7pKEOypMn7sikwCdG7sY
V6AznEEqG6cj5Hk32Oya6PI=
=yg4Z
-END PGP SIGNATURE-

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

  http://archives.postgresql.org/

Re: [GENERAL] Postgres SQL Syntax

[Richard Huxton]

Jim C. wrote:

You probably want one of the mysql converter projects, e.g.
  http://pgfoundry.org/projects/mysql2pgsql/
Also read the "converting from other databases" section here:
  http://www.postgresql.org/docs/techdocs


I tried several conversion tools and did get some minor success with one
or two but mostly they didn't work. The article at this site seems good
at first blush but then one notices that it doesn't seem to have been
updated any time in the last five years.


I have to admit it's been at least a couple of years since I've used any 
of the mysql2pg scripts.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Hardware

[Scott Marlowe]

On Tue, 2007-02-06 at 10:33, Lars Heidieker wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> 
> On 6 Feb 2007, at 15:59, Walter Vaughan wrote:
> 
> > I need to purchase a new server to put posgresql on that will be  
> > acting as the DBMS server for Apache ofBiz soon. While googling  
> > around for performance tweaks I saw this at http://revsys.com/
> > writings/postgresql-performance.html
> >
> > 
> > CPUs — The more CPUs the better, however if your database does not  
> > use many complex functions your money is best spent on a better  
> > disk subsystem. Also, avoid Intel Xeon processors with PostgreSQL  
> > as there is a problem with the context switching in these  
> > processors that gives sub-par performance. Opterons are generally  
> > accepted as being a superior CPU for PostgreSQL databases.
> > 
> >
> > Is this still true in regards to Xeon's? I was looking at a server  
> > with Quad Core Xeon 2 5335 @ 2.0GHz.
> >
> > And at http://www.powerpostgresql.com/PerfList
> >
> 
> It seems to be outdated. As far as I know it was supposed to warn for  
> the HyperThreading things.

It wasn't just hyperthreading.  The older xeons tended to have more CPU
horsepower than cache bandwidth, and this resulted in them going into
swap storms whether hyperthreading was on or not.  It was just easier to
trigger with hyperthreading.

---(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] Cursor timeout in postgres

[Tim Tassonis]

Hi all

When examining strange behaviour in one of my programs I found out that 
I must have somehow gotten into a timeout situation when fetching rows 
from a cursor. My program read the first row, did some stuff for six 
minutes and then tried to fetch the second row, which failed. The 
connection however was still alive and further database access in the 
program worked m'kay.


While I think the responsible component made a good decision to timeout 
after 5 minutes of inactivity, I still wonder who bit me.



My program was written in php, using the odbc interface and the odbc 
driver from postgres compiled against 8.1.5 libpg, accessing a 8.1.5 
database (all on the same machine, on linux 32bit). I have not perfomed 
any relevant tweaking in the config files (apart from php.ini).


Has anybody got the quick answer?

Tim

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

[GENERAL] pg_restore single table privileges/indexes

[Jeff Amiel]

"PostgreSQL 8.2.0 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 
[FreeBSD] 20050518"

We just switched from 'pg_dumpall" to "pg_dump -format=c" for our nightly 
backups.
I wanted to experiment with restoring a single table (if the need should ever 
arise) from the dump file.

I use the --clean option on the restore to drop the table first

pg_restore -U pgsql --table codes  --dbname devl --clean backup.pgsql 

It seems to work fine with no issues.  However, none of the privileges are set.
I checked the dump with grep and sure enough, they are there:

[EMAIL PROTECTED] grep --binary-files=text "GRANT SELECT ON TABLE codes TO 
jboss;" backup.pgsql
GRANT SELECT ON TABLE codes TO jboss;

So...the privileges are in the dump...but do not get restored.  Is this issue 
unique to the --table option?  Is there some way to force it to restore the 
privileges?

I also notice that the indexes are not restored...that I understand.  I guess I 
have to do separate --index for each index on the table, correct?

Any help would be appreciated.

 
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.

[GENERAL] Creating an index on a live database

[John McCawley]

I have a table with a few million rows which has inserts performed on it 
roughly 50 or so times a minute.  It contains a heavily-queried column 
that I would like to add an index to, but I am concerned about a 
deadlock occurring.  Should I wait until downtime to add the index, or 
is the Postgres add index command "nice" enough to avoid deadlocks?



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

  http://archives.postgresql.org/

Re: [GENERAL] pg_restore single table privileges/indexes

[Alan Hodgson]

On Tuesday 06 February 2007 09:32, Jeff Amiel <[EMAIL PROTECTED]> 
wrote:
> We just switched from 'pg_dumpall" to "pg_dump -format=c" for our nightly
> backups. I wanted to experiment with restoring a single table (if the
> need should ever arise) from the dump file.

> I also notice that the indexes are not restored...that I understand.  I
> guess I have to do separate --index for each index on the table, correct?
>
> Any help would be appreciated.

You can extract the full table of contents from the custom dump 
(pg_restore --list dump_file), then extract just the lines relevant to your 
table and use those to restore (or at least identify the objects to 
restore). You'll find the ACL's, indexes, foreign keys and triggers all on 
different lines.

-- 
Ginsberg's Theorem:
 1) You can't win.
 2) You can't break even.
 3) You can't quit the game.


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

   http://archives.postgresql.org/

Re: [GENERAL] Cursor timeout in postgres

[Tom Lane]

Tim Tassonis <[EMAIL PROTECTED]> writes:
> When examining strange behaviour in one of my programs I found out that 
> I must have somehow gotten into a timeout situation when fetching rows 
> from a cursor. My program read the first row, did some stuff for six 
> minutes and then tried to fetch the second row, which failed. The 
> connection however was still alive and further database access in the 
> program worked m'kay.

Failed how, exactly?  Did anything show up in the postmaster log?

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Thomas F. O'Connell]

On Feb 6, 10:33 am, [EMAIL PROTECTED] (Tom Lane) wrote:
> "Thomas F. O'Connell" <[EMAIL PROTECTED]> writes:
>
> > but we just built 8.2.2 from source using cc, and now we're seeing
> > this type of error in the logs:
> > ERROR:  attribute 3 has wrong type
> > DETAIL:  Table has type character varying, but query expects
> > character varying.
>
> This has nothing to do with your compiler :-(

I just wanted to eliminate that variable. It took me a while to track  
down the source of the error in the source.


I saw this nearby thread on -hackers:

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00276.php

The tables in question generating these errors in this database do  
indeed have functional indexes. Is there a known fix, or does this  
qualify as a bug?		


--
Thomas F. O'Connell

optimizing modern web applications
: for search engines, for usability, and for performance :

http://o.ptimized.com/
615-260-0005

Re: [GENERAL] Creating an index on a live database

[Alan Hodgson]

On Tuesday 06 February 2007 09:38, John McCawley <[EMAIL PROTECTED]> 
wrote:
> I have a table with a few million rows which has inserts performed on it
> roughly 50 or so times a minute.  It contains a heavily-queried column
> that I would like to add an index to, but I am concerned about a
> deadlock occurring.  Should I wait until downtime to add the index, or
> is the Postgres add index command "nice" enough to avoid deadlocks?

It won't deadlock, but it will lock out writes for the duration of the index 
creation (the transactions it locks out will just pause until the lock is 
released).  

If you're running 8.2, though, I believe you can CREATE INDEX CONCURRENTLY, 
which will create the index without requiring the write lock on the table.

-- 
Opportunity is missed by most people because it is dressed in overalls and
looks like work. - Thomas Edison


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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL/FireBird

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/06/07 10:59, Scott Marlowe wrote:
> On Tue, 2007-02-06 at 10:19, Tim Tassonis wrote:
[snip]
>> It's been said a million times by BSD advocats: put one line of code 
>> under GPL and you instantly become a willingless slave of Richard 
>> Stallmans hoards of children-eating communists.
> 
> That's ridiculous.  Everybody knows that they're more socialists at
> heart.  

With all their talk about "community this" and "community that",
what else could they be but communists?

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

iD8DBQFFyMH3S9HxQb37XmcRAogNAKDgAaUurihuRAV5OWeAQzjK7IQsrwCeLwg6
Dqfx9RmGRl1UMFHBrXGzyIE=
=Hakk
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/

Re: [GENERAL] Cursor timeout in postgres

[Tim Tassonis]

Hi Tom

Tom Lane wrote:

Tim Tassonis <[EMAIL PROTECTED]> writes:
When examining strange behaviour in one of my programs I found out that 
I must have somehow gotten into a timeout situation when fetching rows 
from a cursor. My program read the first row, did some stuff for six 
minutes and then tried to fetch the second row, which failed. The 
connection however was still alive and further database access in the 
program worked m'kay.


Failed how, exactly?  Did anything show up in the postmaster log?


Forgot to check that, sorry. From my program's point of view, it failed 
silently. When an odbc_exec() succeeds, one generally just loops over 
the rows by odbc_fetch_row(), until it fails. The fetch might have 
returned a message, but this was not checked, as odbc_fetch_row() is 
bound to fail anyway when no more rows are present.


I try to reproduce the situation tomorrow and will also check on any 
odbc_errmsg() messages and the postmaster log.


Bye
Tim


---(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] Cursor timeout in postgres

[Tom Lane]

Tim Tassonis <[EMAIL PROTECTED]> writes:
> I try to reproduce the situation tomorrow and will also check on any 
> odbc_errmsg() messages and the postmaster log.

OK.  A couple of comments: the only timeout within Postgres itself is
statement_timeout, which I think wouldn't apply to your situation even
if you had it set (but I might be wrong).  We have also heard reports
of firewalls dropping idle connections, but in that scenario you'd
not still have a working connection afterwards.

regards, tom lane

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

regression in 8.8.2 [was Re: [GENERAL] Very strange error]

[Andrea Arcangeli]

On Tue, Feb 06, 2007 at 10:09:16AM -0500, Michael Slattery wrote:
> When does this error crop up?  What is the query?  Does this select  
> involve more than one table, or does it involve any homemade  
> functions?  Or overriden functions?

My application broke in a big way with the security update to 8.2.2 so
I hope this is a bug in 8.2.2 and not an intentional breakage of
backwards compatibility in a security update ;).

Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit
more advanced than the plain 8.2.2, but still it's supposedly a stable
branch.

The easiest way for me to reproduce is this:

cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0));
CREATE TABLE
cpushare=> insert into x values (0);
INSERT 0 1
cpushare=> update x set x = 0;
ERROR:  attribute 1 has wrong type
DETAIL:  Table has type numeric, but query expects numeric.
cpushare=> 

Comments welcome. Thanks!

---(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] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

[Bruce Momjian]

Thomas F. O'Connell wrote:
> On Feb 6, 10:33 am, [EMAIL PROTECTED] (Tom Lane) wrote:
>  > "Thomas F. O'Connell" <[EMAIL PROTECTED]> writes:
>  >
>  > > but we just built 8.2.2 from source using cc, and now we're seeing
>  > > this type of error in the logs:
>  > > ERROR:  attribute 3 has wrong type
>  > > DETAIL:  Table has type character varying, but query expects
>  > > character varying.
>  >
>  > This has nothing to do with your compiler :-(
> 
> I just wanted to eliminate that variable. It took me a while to track  
> down the source of the error in the source.
> 
> I saw this nearby thread on -hackers:
> 
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg00276.php
> 
> The tables in question generating these errors in this database do  
> indeed have functional indexes. Is there a known fix, or does this  
> qualify as a bug? 

It is a bug.  There is a patch committed to CVS, and we are working to
figure out how to distribute this fix.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  Homepagehttp://momjian.us
  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

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

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

[GENERAL] daylight savings patches needed?

[Ed L.]

From the FAQ:


1.14) Will PostgreSQL handle recent daylight saving time changes 
in various countries?

PostgreSQL versions prior to 8.0 use the operating system's 
timezone database for daylight saving information. All current 
versions of PostgreSQL 8.0 and later contain up-to-date timezone 
information.


In this context, assuming we have applied the relevant OS patches 
(RHEL, Debian, HPUX 11.11, 11.23), does the phrase "contain 
up-to-date timezone information" mean that all 7.[234].x and 8.x 
installations are prepared to properly handle the March 1, 2007 
DST changes in the US?  If not, what is needed?

TIA.

Ed

---(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: regression in 8.8.2 [was Re: [GENERAL] Very strange error]

[Bruce Momjian]

Andrea Arcangeli wrote:
> On Tue, Feb 06, 2007 at 10:09:16AM -0500, Michael Slattery wrote:
> > When does this error crop up?  What is the query?  Does this select  
> > involve more than one table, or does it involve any homemade  
> > functions?  Or overriden functions?
> 
> My application broke in a big way with the security update to 8.2.2 so
> I hope this is a bug in 8.2.2 and not an intentional breakage of
> backwards compatibility in a security update ;).
> 
> Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit
> more advanced than the plain 8.2.2, but still it's supposedly a stable
> branch.
> 
> The easiest way for me to reproduce is this:
> 
> cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0));
> CREATE TABLE
> cpushare=> insert into x values (0);
> INSERT 0 1
> cpushare=> update x set x = 0;
> ERROR:  attribute 1 has wrong type
> DETAIL:  Table has type numeric, but query expects numeric.
> cpushare=> 
> 
> Comments welcome. Thanks!

This is a known bug in 8.2.2 and we are discussing methods of
distributing the fix as quickly as possible.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  Homepagehttp://momjian.us
  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

---(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: regression in 8.8.2 [was Re: [GENERAL] Very strange error]

[andrea]

On Tue, Feb 06, 2007 at 01:19:28PM -0500, Bruce Momjian wrote:
> This is a known bug in 8.2.2 and we are discussing methods of
> distributing the fix as quickly as possible.

Ok great! Take your time, thanks.

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

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

Re: regression in 8.8.2 [was Re: [GENERAL] Very strange error]

[Alvaro Herrera]

Bruce Momjian wrote:
> Andrea Arcangeli wrote:

> > Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit
> > more advanced than the plain 8.2.2, but still it's supposedly a stable
> > branch.
> > 
> > The easiest way for me to reproduce is this:
> > 
> > cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0));
> > CREATE TABLE
> > cpushare=> insert into x values (0);
> > INSERT 0 1
> > cpushare=> update x set x = 0;
> > ERROR:  attribute 1 has wrong type
> > DETAIL:  Table has type numeric, but query expects numeric.
> > cpushare=> 
> > 
> > Comments welcome. Thanks!
> 
> This is a known bug in 8.2.2 and we are discussing methods of
> distributing the fix as quickly as possible.

The fix is already in the REL8_2_STABLE branch, so Andrea can certainly
update and confirm if his problem is fixed.

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

Re: [GENERAL] daylight savings patches needed?

[Robert Treat]

On Tuesday 06 February 2007 13:16, Ed L. wrote:
> From the FAQ:
>
> 
> 1.14) Will PostgreSQL handle recent daylight saving time changes
> in various countries?
>
> PostgreSQL versions prior to 8.0 use the operating system's
> timezone database for daylight saving information. All current
> versions of PostgreSQL 8.0 and later contain up-to-date timezone
> information.
> 
>
> In this context, assuming we have applied the relevant OS patches
> (RHEL, Debian, HPUX 11.11, 11.23), does the phrase "contain
> up-to-date timezone information" mean that all 7.[234].x and 8.x
> installations are prepared to properly handle the March 1, 2007
> DST changes in the US?  If not, what is needed?
>

If you are running pre-8.0 versions you need to update your operating system 
(as you indicated).  If you running an any 8.x version, you need to be on the 
most current corresponding 8.x.y release.

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

Re: [GENERAL] daylight savings patches needed?

[Bruce Momjian]

Ed L. wrote:
> 
> >From the FAQ:
> 
> 
> 1.14) Will PostgreSQL handle recent daylight saving time changes 
> in various countries?
> 
> PostgreSQL versions prior to 8.0 use the operating system's 
> timezone database for daylight saving information. All current 
> versions of PostgreSQL 8.0 and later contain up-to-date timezone 
> information.
> 
> 
> In this context, assuming we have applied the relevant OS patches 
> (RHEL, Debian, HPUX 11.11, 11.23), does the phrase "contain 
> up-to-date timezone information" mean that all 7.[234].x and 8.x 
> installations are prepared to properly handle the March 1, 2007 
> DST changes in the US?  If not, what is needed?

I was trying to avoid getting into the gory details of which releases
had which timezone fixes, but it seems I can't avoid it.  The new FAQ
item has the details:

USA daylight saving time changes are included in PostgreSQL release
8.0.[4+], and all later major releases, e.g. 8.1.  Canada and Western
Australia changes are included in 8.0.[10+], 8.1.[6+], and all later
major releases.  PostgreSQL releases prior to 8.0 use the operating
system's timezone database for daylight saving information.

If this is unclear, please let me know.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  Homepagehttp://momjian.us
  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

---(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] daylight savings patches needed?

[Bruce Momjian]

Steve Crawford wrote:
> > I was trying to avoid getting into the gory details of which releases
> > had which timezone fixes, but it seems I can't avoid it.  The new FAQ
> > item has the details:
> > 
> > USA daylight saving time changes are included in PostgreSQL release
> > 8.0.[4+], and all later major releases, e.g. 8.1.  Canada and Western
> > Australia changes are included in 8.0.[10+], 8.1.[6+], and all later
> > major releases.  PostgreSQL releases prior to 8.0 use the operating
> > system's timezone database for daylight saving information.
> > 
> > If this is unclear, please let me know.
> > 
> 
> Don't know if this was asked/answered elsewhere but:
> 
> 1. What, exactly, was the point of moving away from the system zoneinfo
> files and requiring PG admins to maintain yet another apparently
> identical set of files? It seemed to work fine as it was and for me this
> change just adds more work and chance of error.

We needed more control of how to query that database for timestamp
support.

> 2. Is there a build option to put it back to the old way?

No.

> 3. If 2.answer=no then would there be any problem just symlinking the PG
> timezone directory to the system zoneinfo directory?

Uh, I think you could do that, yea, assuming the same binaries could be
used.  Remember, it doesn't read the text files but binary representations.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  Homepagehttp://momjian.us
  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

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

   http://archives.postgresql.org/

Re: [GENERAL] daylight savings patches needed?

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Steve Crawford wrote:
>> 1. What, exactly, was the point of moving away from the system zoneinfo
>> files and requiring PG admins to maintain yet another apparently
>> identical set of files?

The fact that not all systems use the zic database.  We were tired of
random system-to-system variations in the timezone behavior ... plus we
needed functionality not exposed by the bog-standard C library API.

>> 3. If 2.answer=no then would there be any problem just symlinking the PG
>> timezone directory to the system zoneinfo directory?

> Uh, I think you could do that, yea, assuming the same binaries could be
> used.  Remember, it doesn't read the text files but binary representations.

I've been thinking of doing that on the Red Hat distro, but the problem
is that we are now behind the curve --- we need to sync with the
upstream zic code's recently added support for 64-bit timezone files
before we can be sure of working with current system-provided databases.
I'd like to get that done for 8.3 ...

regards, tom lane

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

Re: [GENERAL] daylight savings patches needed?

[Peter Eisentraut]

Bruce Momjian wrote:
> USA daylight saving time changes are included in PostgreSQL
> release 8.0.[4+], and all later major releases, e.g. 8.1.  Canada and
> Western Australia changes are included in 8.0.[10+], 8.1.[6+], and
> all later major releases.  PostgreSQL releases prior to 8.0 use the
> operating system's timezone database for daylight saving information.
>
> If this is unclear, please let me know.

Perhaps ">= 8.0.10" would clearer than "8.0.[10+]".

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://archives.postgresql.org/

Re: [GENERAL] daylight savings patches needed?

[Bruce Momjian]

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > USA daylight saving time changes are included in PostgreSQL
> > release 8.0.[4+], and all later major releases, e.g. 8.1.  Canada and
> > Western Australia changes are included in 8.0.[10+], 8.1.[6+], and
> > all later major releases.  PostgreSQL releases prior to 8.0 use the
> > operating system's timezone database for daylight saving information.
> >
> > If this is unclear, please let me know.
> 
> Perhaps ">= 8.0.10" would clearer than "8.0.[10+]".

Yea, I thought about that, but because we do branch selection earlier in
the paragraph, I was afraid people would think that 8.1.1 didn't have
the fix.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  Homepagehttp://momjian.us
  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

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

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

Re: [GENERAL] daylight savings patches needed?

[Steve Crawford]

> I was trying to avoid getting into the gory details of which releases
> had which timezone fixes, but it seems I can't avoid it.  The new FAQ
> item has the details:
> 
> USA daylight saving time changes are included in PostgreSQL release
> 8.0.[4+], and all later major releases, e.g. 8.1.  Canada and Western
> Australia changes are included in 8.0.[10+], 8.1.[6+], and all later
> major releases.  PostgreSQL releases prior to 8.0 use the operating
> system's timezone database for daylight saving information.
> 
> If this is unclear, please let me know.
> 

Don't know if this was asked/answered elsewhere but:

1. What, exactly, was the point of moving away from the system zoneinfo
files and requiring PG admins to maintain yet another apparently
identical set of files? It seemed to work fine as it was and for me this
change just adds more work and chance of error.

2. Is there a build option to put it back to the old way?

3. If 2.answer=no then would there be any problem just symlinking the PG
timezone directory to the system zoneinfo directory?

Cheers,
Steve

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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL/FireBird

[Tomas Vondra]

> On 02/06/07 10:59, Scott Marlowe wrote:
>> On Tue, 2007-02-06 at 10:19, Tim Tassonis wrote:
> [snip]
>>> It's been said a million times by BSD advocats: put one line of code 
>>> under GPL and you instantly become a willingless slave of Richard 
>>> Stallmans hoards of children-eating communists.
>> That's ridiculous.  Everybody knows that they're more socialists at
>> heart.  
> 
> With all their talk about "community this" and "community that",
> what else could they be but communists?

1) As far as I know this should be a technical discussion. I believe
   there are many other forums about politics.

2) I live in a country that was ruled by communist party for more than
   40 years, so I guess a I know a lot of things about that, and that's
   probably the reason why I'm so careful saying someone is a communist.
   I disagree with R. Stallman in many cases but that does not mean he's
   a communist.

Tomas

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

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

Re: [GENERAL] Hardware

[Ben]

On Tue, 6 Feb 2007, Walter Vaughan wrote:



CPUs ? The more CPUs the better, however if your database does not use many 
complex functions your money is best spent on a better disk subsystem. Also, 
avoid Intel Xeon processors with PostgreSQL as there is a problem with the 
context switching in these processors that gives sub-par performance. 
Opterons are generally accepted as being a superior CPU for PostgreSQL 
databases.



Is this still true in regards to Xeon's? I was looking at a server with Quad 
Core Xeon 2 5335 @ 2.0GHz.


My understanding is that this is no longer true with the newer xeons, but 
then, I haven't tested them myself, so I can't say.


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

[GENERAL] getting status transaction error

[Merlin Moncure]

around 6:30 this morning, I started getting the following messages in my log:

Feb  6 06:33:34 mojo postgres[1117]: [2-1] :: ERROR:  could not access
status of transaction 51911
Feb  6 06:34:35 mojo postgres[1128]: [2-1] :: ERROR:  could not access
status of transaction 51911
[...]

repeated roughly once a minute.  I've never seen this before. this is
on postgres 8.1.1 running on fedora core 4 smp.  I don't have any
contextual information yet but I'm getting ready to turn statement
logging on.

Anybody know what this is?

merlin

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

  http://archives.postgresql.org/

Re: [GENERAL] getting status transaction error

[Merlin Moncure]

On 2/6/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:

around 6:30 this morning, I started getting the following messages in my log:

Feb  6 06:33:34 mojo postgres[1117]: [2-1] :: ERROR:  could not access
status of transaction 51911
Feb  6 06:34:35 mojo postgres[1128]: [2-1] :: ERROR:  could not access
status of transaction 51911
[...]

repeated roughly once a minute.  I've never seen this before. this is
on postgres 8.1.1 running on fedora core 4 smp.  I don't have any
contextual information yet but I'm getting ready to turn statement
logging on.

Anybody know what this is?

[x-posting to -hackers]

actually, here is some more relevant bits from the log.
Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
processing database "template0"
Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
status of transaction 51911
Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
file "pg_clog/0207": No such file or directory

repeated ad-naseum

merlin

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

Re: [GENERAL] [HACKERS] getting status transaction error

[Stefan Kaltenbrunner]

Merlin Moncure wrote:
> On 2/6/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
>> around 6:30 this morning, I started getting the following messages in
>> my log:
>>
>> Feb  6 06:33:34 mojo postgres[1117]: [2-1] :: ERROR:  could not access
>> status of transaction 51911
>> Feb  6 06:34:35 mojo postgres[1128]: [2-1] :: ERROR:  could not access
>> status of transaction 51911
>> [...]
>>
>> repeated roughly once a minute.  I've never seen this before. this is
>> on postgres 8.1.1 running on fedora core 4 smp.  I don't have any
>> contextual information yet but I'm getting ready to turn statement
>> logging on.
>>
>> Anybody know what this is?
> [x-posting to -hackers]
> 
> actually, here is some more relevant bits from the log.
> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> processing database "template0"
> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> status of transaction 51911
> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> file "pg_clog/0207": No such file or directory

hmm I first thought it could have been
http://archives.postgresql.org/pgsql-committers/2006-01/msg00288.php
which affects 8.1.1 but that's not the very same error as the one
created by the above bug.


Stefan

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

Re: [GENERAL] getting status transaction error

[Alvaro Herrera]

Merlin Moncure wrote:
> On 2/6/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> >around 6:30 this morning, I started getting the following messages in my 
> >log:
> >
> >Feb  6 06:33:34 mojo postgres[1117]: [2-1] :: ERROR:  could not access
> >status of transaction 51911
> >Feb  6 06:34:35 mojo postgres[1128]: [2-1] :: ERROR:  could not access
> >status of transaction 51911
> >[...]
> >
> >repeated roughly once a minute.  I've never seen this before. this is
> >on postgres 8.1.1 running on fedora core 4 smp.  I don't have any
> >contextual information yet but I'm getting ready to turn statement
> >logging on.
> >
> >Anybody know what this is?
> [x-posting to -hackers]
> 
> actually, here is some more relevant bits from the log.
> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> processing database "template0"
> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> status of transaction 51911
> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> file "pg_clog/0207": No such file or directory

I guess the problem here is that autovacuum believes that template0
needs a database-wide vacuum due to Xid wraparound getting closer.  And
that database seems to have Xid 51911 somewhere, the clog bit for
which was in the 0207 file which was deleted some time ago.

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

Re: [GENERAL] [HACKERS] getting status transaction error

[Merlin Moncure]

On 2/6/07, Stefan Kaltenbrunner <[EMAIL PROTECTED]> wrote:

Merlin Moncure wrote:
> On 2/6/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
>> around 6:30 this morning, I started getting the following messages in
>> my log:
>>
>> Feb  6 06:33:34 mojo postgres[1117]: [2-1] :: ERROR:  could not access
>> status of transaction 51911
>> Feb  6 06:34:35 mojo postgres[1128]: [2-1] :: ERROR:  could not access
>> status of transaction 51911
>> [...]
>>
>> repeated roughly once a minute.  I've never seen this before. this is
>> on postgres 8.1.1 running on fedora core 4 smp.  I don't have any
>> contextual information yet but I'm getting ready to turn statement
>> logging on.
>>
>> Anybody know what this is?
> [x-posting to -hackers]
>
> actually, here is some more relevant bits from the log.
> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> processing database "template0"
> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> status of transaction 51911
> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> file "pg_clog/0207": No such file or directory

hmm I first thought it could have been
http://archives.postgresql.org/pgsql-committers/2006-01/msg00288.php
which affects 8.1.1 but that's not the very same error as the one
created by the above bug.


ya, it doesn't seem to match, as this seems to be repeating quite
regularly.  interesting that my 'clog' files start at 06B6 and count
up. 0207 is way off the charts.

a lot of applications are hitting this database, and so far everything
seems to be running ok (i found this log msg by accident), but I am
now officially very nervous.

merlin

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

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

Re: [GENERAL] getting status transaction error

[Merlin Moncure]

On 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

> actually, here is some more relevant bits from the log.
> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> processing database "template0"
> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> status of transaction 51911
> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> file "pg_clog/0207": No such file or directory

I guess the problem here is that autovacuum believes that template0
needs a database-wide vacuum due to Xid wraparound getting closer.  And
that database seems to have Xid 51911 somewhere, the clog bit for
which was in the 0207 file which was deleted some time ago.


Latest checkpoint's NextXID: 2162841139
2^31:   2147483648

is this related?
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

Re: [GENERAL] replication choices

[Ben]

Yeah, log shipping looks like it solves the network problem, except for 
the part about how how I must replicate to a normal slony node before I 
can get logs to ship. We don't have the hardware to have a secondary 
database at every site. :(


On Tue, 6 Feb 2007, Andrew Sullivan wrote:


On Thu, Jan 25, 2007 at 12:17:52PM -0800, Ben wrote:

familiar with Slony, and from what I understand, using Slony with bad
networks leads to bad problems. I'm also not sure that Slony supports
replicating from multiple sources to the same postgres install, even if
each replication process is writing to a different schema.


Yes, you can have multiple origins into the same database, without a
problem.  I'd be worried for sure about the network unreliability,
though.  You might, however, be able to do this usefully using the
log shipping features of Slony.

I would _not_ worry about the outbound replication from the centre,
assuming that the changes are infrequent.

A

--
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
   --Brad Holland

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

  http://archives.postgresql.org/



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

Re: [GENERAL] [HACKERS] getting status transaction error

[Alvaro Herrera]

Merlin Moncure wrote:

> ya, it doesn't seem to match, as this seems to be repeating quite
> regularly.  interesting that my 'clog' files start at 06B6 and count
> up. 0207 is way off the charts.
> 
> a lot of applications are hitting this database, and so far everything
> seems to be running ok (i found this log msg by accident), but I am
> now officially very nervous.

I don't think there's much cause for concern here.  If my theory is
correct, this is an autovacuum bug which was fixed in 8.1.7.

What I'd do is create a 0207 clog file, fill it with 0x55 (which is
"transactions committed" for all transactions in that interval), and do
a VACUUM FREEZE on that database.  You'll need to set
pg_database.datallowconn=true beforehand.

Of course, I'd copy the files somewhere else and experiment on a scratch
postmaster, running on a different port, just to be sure ...

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

Re: [GENERAL] [HACKERS] getting status transaction error

[Merlin Moncure]

n 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

Merlin Moncure wrote:

> ya, it doesn't seem to match, as this seems to be repeating quite
> regularly.  interesting that my 'clog' files start at 06B6 and count
> up. 0207 is way off the charts.
>
> a lot of applications are hitting this database, and so far everything
> seems to be running ok (i found this log msg by accident), but I am
> now officially very nervous.

I don't think there's much cause for concern here.  If my theory is
correct, this is an autovacuum bug which was fixed in 8.1.7.

What I'd do is create a 0207 clog file, fill it with 0x55 (which is
"transactions committed" for all transactions in that interval), and do
a VACUUM FREEZE on that database.  You'll need to set
pg_database.datallowconn=true beforehand.

Of course, I'd copy the files somewhere else and experiment on a scratch
postmaster, running on a different port, just to be sure ...


thats a big help, database is actually fairly huge, so I may have to
just go ahead and do it.   I'm off to a meeting, but I'll check back
when I'm done and assuming nobody else says 'don't do that', I'll try
the fix and post back with the result.

thanks all,
merlin

---(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: [HACKERS] [GENERAL] getting status transaction error

[Alvaro Herrera]

Merlin Moncure wrote:
> On 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> >> actually, here is some more relevant bits from the log.
> >> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> >> processing database "template0"
> >> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> >> status of transaction 51911
> >> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> >> file "pg_clog/0207": No such file or directory
> >
> Latest checkpoint's NextXID: 2162841139
> 2^31:   2147483648

I think the relevant arithmetic here is

echo "2162841139 51911 - p" | dc
1618399228

That's a billion and a half transactions.  Autovacuum uses the formula

this_whole_db = (tmp->age >
 (int32) ((MaxTransactionId >> 3) * 3 - 10));

to determine whether it needs database-wide vacuum. 
(MaxTransactionId >> 3) is 536870911, so the calculation is
536870911 * 3 - 10

echo "536870911 3 * 10 - p" | dc
1610512733

which looks awfully close to the number above.  About 7 million
transactions must have passed since the first time the error showed up
-- does that sound likely?

Well, scratch that -- what's the _current_ Xid? (not lastest
checkpoint's)

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

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

Re: [GENERAL] [HACKERS] getting status transaction error

[Alvaro Herrera]

Merlin Moncure wrote:
> n 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> >Merlin Moncure wrote:
> >
> >> ya, it doesn't seem to match, as this seems to be repeating quite
> >> regularly.  interesting that my 'clog' files start at 06B6 and count
> >> up. 0207 is way off the charts.
> >>
> >> a lot of applications are hitting this database, and so far everything
> >> seems to be running ok (i found this log msg by accident), but I am
> >> now officially very nervous.
> >
> >I don't think there's much cause for concern here.  If my theory is
> >correct, this is an autovacuum bug which was fixed in 8.1.7.
> >
> >What I'd do is create a 0207 clog file, fill it with 0x55 (which is
> >"transactions committed" for all transactions in that interval), and do
> >a VACUUM FREEZE on that database.  You'll need to set
> >pg_database.datallowconn=true beforehand.
> >
> >Of course, I'd copy the files somewhere else and experiment on a scratch
> >postmaster, running on a different port, just to be sure ...
> 
> thats a big help, database is actually fairly huge, so I may have to
> just go ahead and do it.   I'm off to a meeting, but I'll check back
> when I'm done and assuming nobody else says 'don't do that', I'll try
> the fix and post back with the result.

Well, you don't need to copy all databases for the test area, just the
base/ dir for template0 (along with all pg_xlog and pg_clog files,
etc, but these shouldn't be as big as all the other stuff in base/).

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

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

   http://archives.postgresql.org/

Re: [GENERAL] partitioning / rules - strange behavior

[Tomas Vondra]

> If there´s only the insert_850 RULE then everything works as expected - 
> the
> insert prints "INSERT 0 0", the row is inserted into the correct partition
> which is sessions_850 - I can fetch it using either
> 
> SELECT * FROM sessions WHERE id = currval('sessions_id_seq');
> 
> or direcly by
> 
> SELECT * FROM sessions_850 WHERE id = currval('sessions_id_seq');
> 
> When I create the next next rule (insert_900 for ids between 900 and
> 949) it stops working - it prints "INSERT 0 0" just as before, everything
> seems fine, but the row disappears - it's not available .
> 
> I'm not sure about the query plans, but I think I've checked that and 
> everything
> seemed ok - all the partitions were used as far as I remember. But this
> shouldn't be a problem as we have not reached the 900 limit yet (so the 
> new
> partition is not used at all). And we've tried to restart the PostgreSQL as 
> the
> last hope, yesterday, so there really should be no old plans.
> 
> I don't have an access to the production database (I have not been able to
> simulate this on the development/testing system) - I'll play with that at 
> night
> (european time). I'll try to drop / recreate the partition (I've tried to
> recreate only the RULEs, not the partitions).
> 
> Tomas

OK, I did some tests a while ago and the bad news is I still was not
able to fix it. The table structure is this

===

db=> \d sessions
   Table "public.sessions"
 Column  |Type |
   Modifiers
--+-+-
id   | integer | not null default
nextval('sessions_id_seq'::regclass)
browser_id   | integer |
os_id| integer |
arch_id  | integer |
language_id  | character(2)|
country_id   | character(2)|
visitor_id   | integer | not null
ip   | inet| not null
ip_forward   | inet|
session_date | timestamp without time zone | not null default now()
user_agent   | character varying(255)  |
screen_width | smallint|
screen_height| smallint|
screen_bit_depth | smallint|
javascript   | boolean | default false
browser_minor| character varying(16)   |
browser_major| character varying(16)   |
referer  | text|
last_action  | integer | not null default 0
Indexes:
   "sessions_pkey" PRIMARY KEY, btree (id)
Check constraints:
   ... some foreign keys, not important here ...
Rules:
   insert_850 AS
   ON INSERT TO sessions
  WHERE new.id >= 850 AND new.id <= 899 DO INSTEAD  INSERT INTO
sessions_850 (id, browser_id, os_id, arch_id, language_id,
country_id, visitor_id, ip, ip_forward, session_date, user_agent,
screen_width, screen_height, screen_bit_depth, javascript,
browser_minor, browser_major, referer, last_action)
 VALUES (new.id, new.browser_id, new.os_id, new.arch_id,
new.language_id, new.country_id, new.visitor_id, new.ip, new.ip_forward,
new.session_date, new.user_agent, new.screen_width, new.screen_height,
new.screen_bit_depth, new.javascript, new.browser_minor,
new.browser_major, new.referer, new.last_action)

===

We're using sequence to generate the sessions(id) value, but that should
not be a problem - with the structure / rules everything works fine (the
current value in sessions_id_seq is about 870 so the values are
inserted into the sessions_850 partition).

The I create the 'next partition' for values between 900 and 949
 using

===

   CREATE TABLE sessions_900 (
 CHECK (id BETWEEN 900 AND 949),
 PRIMARY KEY (id)
   ) INHERITS (sessions);

===

and everything still seems fine, even the execution plans reflect this
new child table:

===

db=> explain select * from sessions;
 QUERY PLAN
--
Result  (cost=0.00..52262.48 rows=1052924 width=775)
  ->  Append  (cost=0.00..52262.48 rows=1052924 width=775)
->  Seq Scan on sessions  (cost=0.00..12.00 rows=100 width=775)
->  Seq Scan on sessions_800 sessions  (cost=0.00..23128.78
rows=500539 width=280)
->  Seq Scan on sessions_850 sessions  (cost=0.00..6147.60
rows=51230 width=775)
->  Seq Scan on sessions_900 sessions  (cost=0.00..12.00
rows=100 wid

[GENERAL] tsearch2 parser configuration

[Worky Workerson]

Is it possible to configure the tsearch2 parser?  I'd like a very dumb
parser that splits on everything that is not [a-zA-Z0-9.].  The
default parser seems to work well on my dataset except for the '/'
character ... it doesn't split mike/john into two lexemes.  And ideas?

Thanks!

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

[GENERAL] 8.2.2 Announcement?

[Thomas F. O'Connell]

Shouldn't there be an announcement about the buggy 8.2.2 announced  
yesterday preceding the availability of new binaries, or is the bug  
not considered severe enough to invalidate the 8.2.2 sources that are  
currently in distribution?


--
Thomas F. O'Connell

optimizing modern web applications
: for search engines, for usability, and for performance :

http://o.ptimized.com/
615-260-0005

Re: [GENERAL] PostgreSQL/FireBird

[Scott Marlowe]

On Tue, 2007-02-06 at 11:59, Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 02/06/07 10:59, Scott Marlowe wrote:
> > On Tue, 2007-02-06 at 10:19, Tim Tassonis wrote:
> [snip]
> >> It's been said a million times by BSD advocats: put one line of code 
> >> under GPL and you instantly become a willingless slave of Richard 
> >> Stallmans hoards of children-eating communists.
> > 
> > That's ridiculous.  Everybody knows that they're more socialists at
> > heart.  
> 
> With all their talk about "community this" and "community that",
> what else could they be but communists?

I think you missed my joke there...

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

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

Re: [GENERAL] PostgreSQL/FireBird

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/06/07 14:51, Scott Marlowe wrote:
> On Tue, 2007-02-06 at 11:59, Ron Johnson wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 02/06/07 10:59, Scott Marlowe wrote:
>>> On Tue, 2007-02-06 at 10:19, Tim Tassonis wrote:
>> [snip]
 It's been said a million times by BSD advocats: put one line of code 
 under GPL and you instantly become a willingless slave of Richard 
 Stallmans hoards of children-eating communists.
>>> That's ridiculous.  Everybody knows that they're more socialists at
>>> heart.  
>> With all their talk about "community this" and "community that",
>> what else could they be but communists?
> 
> I think you missed my joke there...

I thought what you said was funny.  Maybe my sense of humor is off?

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

iD8DBQFFyOwDS9HxQb37XmcRAr85AKCb5Hy6YEcTd4nE7o/8UgyHmRPP3ACfVo4y
H4brUt/9sIXj3ExA0ujG8PA=
=BdX3
-END PGP SIGNATURE-

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

Re: [GENERAL] PostgreSQL/FireBird

[Tomas Vondra]

>> With all their talk about "community this" and "community that",
>> what else could they be but communists?
> 
> I think you missed my joke there...

Yeah, Ron Johnson wrote me about that already. Sorry for that, I'm
probably too touchy when it comes to marking someone as a communist,
especially in the area of open source, and I have missed the crank.

Tomas

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

Re: [GENERAL] 8.2.2 Announcement?

[Bruce Momjian]

Thomas F. O'Connell wrote:
> Shouldn't there be an announcement about the buggy 8.2.2 announced  
> yesterday preceding the availability of new binaries, or is the bug  
> not considered severe enough to invalidate the 8.2.2 sources that are  
> currently in distribution?

The sources and binaries have been pulled from the web site.
I am working on an announcement now.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  Homepagehttp://momjian.us
  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

---(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] Postgres SQL Syntax

[Richard Huxton]

Jim C. wrote:

Richard Huxton wrote:

Jim C. wrote:

You probably want one of the mysql converter projects, e.g.
  http://pgfoundry.org/projects/mysql2pgsql/
Also read the "converting from other databases" section here:
  http://www.postgresql.org/docs/techdocs

I tried several conversion tools and did get some minor success with one
or two but mostly they didn't work. The article at this site seems good
at first blush but then one notices that it doesn't seem to have been
updated any time in the last five years.


Techdocs are user-contributed. Many are single articles.


I have to admit it's been at least a couple of years since I've used any
of the mysql2pg scripts.


Perhaps this might be an issue the developers wish to address?


"The" developers? The various pgfoundry projects all have their own 
developers. If you think about it, core PostgreSQL developers are 
unlikely to have a lot of MySQL databases to convert, so they'd be a 
poor choice to build such a tool anyway.


> No

sarcasm here.  I know they are busy and may not have the resources any
time soon. If it hasn't been much under consideration for several years
they may want to think about it though.


Well, I said that *I* haven't used any for a couple of years, but 
mysql2pgsql seems to have been updated *today*.

  http://pgfoundry.org/projects/mysql2pgsql/
What I'd suggest is running it, identifying what doesn't work for you, 
and submitting a bug-report. Better still, a bug-report with a patch. 
I'm sure Jose and Joe would appreciate any improvements.


--
  Richard Huxton
  Archonet Ltd

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

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

Re: [HACKERS] [GENERAL] getting status transaction error

[Merlin Moncure]

On 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

Merlin Moncure wrote:
> On 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> >> actually, here is some more relevant bits from the log.
> >> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> >> processing database "template0"
> >> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> >> status of transaction 51911
> >> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> >> file "pg_clog/0207": No such file or directory
> >
> Latest checkpoint's NextXID: 2162841139
> 2^31:   2147483648

I think the relevant arithmetic here is

echo "2162841139 51911 - p" | dc
1618399228

That's a billion and a half transactions.  Autovacuum uses the formula

this_whole_db = (tmp->age >
 (int32) ((MaxTransactionId >> 3) * 3 - 10));

to determine whether it needs database-wide vacuum.
(MaxTransactionId >> 3) is 536870911, so the calculation is
536870911 * 3 - 10

echo "536870911 3 * 10 - p" | dc
1610512733

which looks awfully close to the number above.  About 7 million
transactions must have passed since the first time the error showed up
-- does that sound likely?

Well, scratch that -- what's the _current_ Xid? (not lastest
checkpoint's)


I don't know any better way to get that than this:
postgres=# insert into foo default values;
INSERT 0 1
postgres=# select xmin,xmax from foo;
   xmin| xmax
+--
2163877346 |0
(1 row)

merlin

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

  http://archives.postgresql.org/

[GENERAL] Npgsql and 57014 query_canceled error message

[Karen Hill]

I'm doing some testing on a larger dataset, and I've started getting a
57014 error message when I catch an NpgsqlException.  I thought it
might be timing out on me, so in the connection string I've set the
time out settings to the maximum of 1024 seconds before timeout.

Has anyone else experienced this, and is there a known solution?
Thank you.

regards,
Karen


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

[GENERAL] Array OUT columns in a record returned from a function

[Ged]

I have a web site devoted to quest guides for World of Warcraft
players. There is a view that gets all the data needed for the quest
details page from several tables. The view currently looks like this
(joins indicating non-null columns and left joins indicating nullable
columns):

SELECT q.id, q.name, q.series, r.name AS series_name, q.summary,
q.instructions, q.experience, q.notes, st.id AS stage, st.description
AS warning, s.id AS sid, s.name AS starts, s.description AS sdesc,
e.id AS eid, e.name AS ends, e.description AS edesc,
COALESCE('/zones/'::text || q."zone"::text, '/instances/'::text ||
q.instance::text) AS "location", COALESCE(z.name, i.name) AS
location_name, sz.name AS szone, ez.name AS ezone, si.name AS
sinstance, ei.name AS einstance
  FROM quests q
  JOIN stages st ON st.id = q.stage
  JOIN npcs s ON s.id = q.starts
  JOIN npcs e ON e.id = q.ends
  LEFT JOIN series r ON r.id = q.series
  LEFT JOIN zones z ON z.id = q."zone"
  LEFT JOIN instances i ON i.id = q.instance
  LEFT JOIN zones sz ON sz.id = s."zone"
  LEFT JOIN zones ez ON ez.id = e."zone"
  LEFT JOIN instances si ON si.id = s.instance
  LEFT JOIN instances ei ON ei.id = e.instance;

Now it turns out that this view is based on an old analysis of the
problem domain which is proving unrealistic.

For now, we'll stick to just one change: I imagined that each quest
would have only one "quest-giver", so I could use a foreign key column
("starts") in the quests table pointing to the npcs table.

It turns out that a few quests can be given by several npcs (and npcs
in general will give multiple quests) so I want to move to a link
table approach.

Now a link table means that the query will return several records, one
for each quest giver, where the quest data will reman the same and the
npc data will differ. I've seen and maintained old VB code that takes
this approach, filling the master form fields using just the first
record and the detail form fields using all the records, and while it
works it's never impressed me as being particularly elegant. And of
course the problem will only get compounded when I have to add
multiple quest-enders too.

My proposed solution is to replace the view with a function returning
a single record. The quest starter fields sid, starts, sdesc, szone
and sinstance will become arrays, and I'll fill them in using a
separate query.

So I settled on a plan of action, with testing at each step:

1) Convert the current view to a function returning a record, without
changing anything.

2) Separate out the population of the quest starter and quest ender
details into new queries. The queries still return only one record,
based on the old fk fields.

3) Change the field types of the quest starter and quest ender fields
to arrays. Keep the quests that select into them the same as before,
except that they now select into the 0th element of the arrays.

4) Change the quests that select into the starter and ender details
fields so that they return multiple records where appropriate. It
would be lovely if postgres automatically noticed that there were
several rows in the results and the targets were arrays, and just took
care of it, but more likely loop through the rows and stuff the values
into the arrays manually.

5) Champagne!

I got as far as step 2, and that works perfectly, but I'm getting a
mysterious error at step 3. The function and its associated data type
look like this at step 3:


CREATE TYPE foo_type AS
(
   id  INTEGER
,   nameVARCHAR(60)
,   summary TEXT
,   instructionsTEXT
,   experience  TEXT
,   notes   TEXT
,   starts  INTEGER[]
,   endsINTEGER
,   stage   INTEGER
,   warning VARCHAR(100)
,   sname   VARCHAR(40)[]
,   sdesc   TEXT[]
,   slocation   VARCHAR(40)[]
,   ename   VARCHAR(40)
,   edesc   TEXT
,   elocation   VARCHAR(40)
,   series  INTEGER
,   series_name VARCHAR(40)
,   locationTEXT
,   location_name   TEXT
)
;


DECLARE
results foo_type;

BEGIN

SELECT
q.id, q.name, q.summary, q.instructions, q.experience, q.notes,
q.starts, q.ends
,   q.stage, st.description
,   q.series, r.name
,   COALESCE('../zones/' || q.zone, '../instances/' || q.instance)
,   COALESCE(z.name, i.name)
INTO
results.id
,   results.name
,   results.summary
,   results.instructions
,   results.experience
,   results.notes
,   results.starts[0]
,   results.ends
,   results.stage
,   results.warning
,   results.series
,   results.series_name
,   results.location
,   results.location_name
FROM
quests q
JOIN stages st ON st.id = q.stage
JOIN npcs e ON e.id = q.ends
LEFT JOIN series r ON r.id = q.series
LEFT JOIN zones z ON z.id = q.zone
LEFT JOIN instances i ON i.id 

[GENERAL] pg_dump/restore and functions/triggers/trigger functions

[Jeff Amiel]

did a pg_dump --format=c for a production database (on a 8.1.2 server) and 
attempted to pg_restore on a 8.2.0 server.

Things seemed to go fine with the exception of functions, triggers and trigger 
functions.

It was apparently doing a bunch of ACL work towards the end and spewed a slew 
of errors like this:


Feb  6 16:23:02 devl-app1 postgres[22552]: [624-1] ERROR:  function 
add_entity(text, text, integer, text) does not exist
Feb  6 16:23:02 devl-app1 postgres[22552]: [624-2] STATEMENT:  REVOKE ALL ON 
FUNCTION add_entity(text, text, integer, text) FROM PUBLIC;
so I checks the TOC to ensure that the function and ACLs were listed

[EMAIL PROTECTED] pg_restore -l prod-app-1.pgsql-prod.pgsql | grep add_entity
320; 1255 16412 FUNCTION public add_entity(text, text, integer, text) pgsql
3318; 0 0 ACL public add_entity(text, text, integer, text) pgsql

sure enoughthe function and the ACLs were there.  
Hmmm...When all was said and done, the only functions in the database were ones 
that originally came from pg_crypto (from template0 when I created the new 
database to load the data into)

Also...similar problem with triggers/trigger functions


Feb  6 16:07:27 devl-app1 postgres[22552]: [509-1] ERROR:  function 
batch_stat_populate() does not exist
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-2] STATEMENT:  CREATE TRIGGER 
batch_stats_updater
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-3]  AFTER INSERT ON 
batch_audit
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-4]  FOR EACH ROW
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-5]  EXECUTE PROCEDURE 
batch_stat_populate();

What up?  is this a version inconsistency issue?
Please tell me I don't have to -l into a listfile and manually re-order items 
and the use -L ?





 
-
Sucker-punch spam with award-winning protection.
 Try the free Yahoo! Mail Beta.

Re: [GENERAL] pg_dump/restore and functions/triggers/trigger functions

[Jeff Amiel]

The original pg_dump used --schema="public" .

Could the fact that pg_catalog or information_schema weren't included cause 
these kinds of issues? (I can't imagine why)


 <[EMAIL PROTECTED]> wrote: did a pg_dump --format=c for a production database 
(on a 8.1.2 server) and attempted to pg_restore on a 8.2.0 server.

Things seemed to go fine with the exception of functions, triggers and trigger 
functions.

It was apparently doing a bunch of ACL work towards the end and spewed a slew 
of errors like this:


Feb  6 16:23:02 devl-app1 postgres[22552]: [624-1] ERROR:  function 
add_entity(text, text, integer, text) does not exist
Feb  6 16:23:02 devl-app1 postgres[22552]: [624-2] STATEMENT:  REVOKE ALL ON 
FUNCTION add_entity(text, text, integer, text) FROM PUBLIC;
so I checks the TOC to ensure that the function and ACLs were listed

[EMAIL PROTECTED] pg_restore -l prod-app-1.pgsql-prod.pgsql | grep add_entity
320; 1255 16412 FUNCTION public add_entity(text, text, integer, text) pgsql
3318; 0 0 ACL public add_entity(text, text, integer, text) pgsql

sure enoughthe function and the ACLs were there.   
Hmmm...When all was said and done, the only functions in the database were ones 
that originally came from pg_crypto (from template0 when I created the new 
database to load the data into)

Also...similar problem with triggers/trigger functions


Feb  6 16:07:27 devl-app1 postgres[22552]: [509-1] ERROR:  function 
batch_stat_populate() does not exist
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-2] STATEMENT:  CREATE TRIGGER 
batch_stats_updater
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-3]  AFTER INSERT ON 
batch_audit
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-4]  FOR EACH ROW
Feb  6 16:07:27 devl-app1 postgres[22552]: [509-5]  EXECUTE PROCEDURE 
batch_stat_populate();

What up?  is this a version inconsistency issue?
Please tell me I don't have to -l into a listfile and manually re-order items 
and the use -L ?




   

-
Sucker-punch spam with award-winning protection.
 Try the free Yahoo! Mail Beta.

 
-
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives. Check it out.

Re: [GENERAL] Array OUT columns in a record returned from a function

[Tom Lane]

Ged <[EMAIL PROTECTED]> writes:
> SELECT
>   q.id, q.name, q.summary, q.instructions, q.experience, q.notes,
> q.starts, q.ends
> , q.stage, st.description
> , q.series, r.name
> , COALESCE('../zones/' || q.zone, '../instances/' || q.instance)
> , COALESCE(z.name, i.name)
> INTO
>   results.id
> , results.name
> ,   results.summary
> ,   results.instructions
> ,   results.experience
> ,   results.notes
> , results.starts[0]
> ,   results.ends
> ,   results.stage
> ,   results.warning
> ,   results.series
> ,   results.series_name
> ,   results.location
> ,   results.location_name
> FROM

I don't think an INTO target can be anything except a simple variable or
simple record field.  So the [0] is taken as part of the SQL query
rather than part of an INTO target, and it goes downhill from there.

Consider assigning that value to a temp variable and then assigning
into the desired result format.

regards, tom lane

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

Re: [GENERAL] pg_dump/restore and functions/triggers/trigger functions

[Tom Lane]

Jeff Amiel <[EMAIL PROTECTED]> writes:
> did a pg_dump --format=c for a production database (on a 8.1.2 server) and 
> attempted to pg_restore on a 8.2.0 server.

> Things seemed to go fine with the exception of functions, triggers and 
> trigger functions.

Seems pretty strange.  Can you strip this down to a reproducible test
case?

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] pg_dump/restore and functions/triggers/trigger functions

[Tom Lane]

Jeff Amiel <[EMAIL PROTECTED]> writes:
> The original pg_dump used --schema="public" .

I think that would have excluded anything that didn't demonstrably
belong to schema public, such as procedural languages.  Is it possible
that *all* your functions failed to load, and you only noted the ensuing
GRANT/REVOKE failures?

regards, tom lane

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