[GENERAL] migrating from MSSQL

[Eugene .]

Hi all,

I've been tasked with evaluating the feasibility of migrating our in-house 
application from MSSQL to PostgreSQL. It is fairly old and has lots of stored 
procedures, which is why we need to think carefully before making the switch. 
Does anyone else have a similar experience? What are some of the things to 
watch out for?

Secondly, which commercial support vendor would you recommend? I found 
EnterpriseDB and CommandPrompt, but I don't know anything about them. Any other 
candidates?

thanks,

Eugene



  

[GENERAL] migrating from MSSQL

[Eugene .]

Hi all,

I've been tasked with evaluating the feasibility of migrating our in-house 
application from MSSQL to PostgreSQL.
It is fairly old and has lots of stored procedures, which is why we
need to think carefully before making the switch. Does anyone else have
a similar experience? What are some of the things to watch out for?

Secondly, which commercial support vendor would you recommend? I found 
EnterpriseDB and CommandPrompt, but I don't know anything about them. Any other 
candidates?

thanks,

Eugene



  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[John R Pierce]

Eugene . wrote:

Hi all,

I've been tasked with evaluating the feasibility of migrating our 
in-house application from MSSQL to PostgreSQL. It is fairly old and 
has lots of stored procedures, which is why we need to think carefully 
before making the switch. Does anyone else have a similar experience? 
What are some of the things to watch out for?




from what all I gather, transact*SQL is -way- different, so you'll be 
pretty much rewriting your procedures, you might consider just 
rearchitecting the whole application.A lot of people are moving 
their business logic out of stored procedures and into a application 
server, programmed in a conventional language, such as Java, or Ruby, or 
whatever, and just using stored procedures where it makes sense for 
performance.


Secondly, which commercial support vendor would you recommend? I found 
EnterpriseDB and CommandPrompt, but I don't know anything about them. 
Any other candidates?


Those are both fine upstanding members of the postgres community, each 
employs a number of primary project developers, and both have a long 
history of providing support.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[Gevik Babakhani]

Hi

I have migrated a very large database from MSSQL to PG. This was a 
database of +/- 400 tables. You should note the following.


- MSSQL datatypes are not always compatible with PG datatypes. If you do 
this carefully PG will save you a lot of time.
- The "timestamp" datatype of MSSQL is not the same thing in PG. The 
"timestamp" is from MSSQL 7 and 2000 days
- Check if all the built-in functions that you are using in MSSQL also 
are available in PG (most are by the way)
- Auto numbering is MSSQL is different from PG. In PG you have to do 
with sequences (serial datatype)


- About the stored procedures:

The first thing you will notice in migrating stored procedures is that 
in PG you will have to define what the return type will be of your 
stored procedure.
This might be a little frustrating because if you have a stored 
procedure that returns a set of columns which belong to various tables, 
you then have to create a custom type that exactly matches your stored 
procedures number (and type) of columns or use the "record" datatype as 
return type. (I can tell you that this is not very handy)


Please note that TSQL is a very different language. In my case 70% of 
all the stored procedures had to be customized one way or another.


Entity names in PostgreSQL are down folded by default: CREATE TABEL 
MyTable  results  the table  name to be  mytable (lower case).
This causes a lot of problems if your application (especially C/C++ apps 
) expects the column names to be case preserved.
In order to achieve case preserving in PG you must do: CREATE TABLE 
"MyTable" (note the quotes around the table name)


- I have had no problems migrating constraints and indexes.
- Triggers are also different but very easy to migrate.
- I had to write a little application for migrating data. because I had 
to check and modify data before I inserted them into PG. Perhaps you can 
export the data to CSV and then import it in PG using the COPY command.


I hope this helps,

Regards,
Gevik.


Eugene . wrote:

Hi all,

I've been tasked with evaluating the feasibility of migrating our 
in-house application from MSSQL to PostgreSQL. It is fairly old and 
has lots of stored procedures, which is why we need to think carefully 
before making the switch. Does anyone else have a similar experience? 
What are some of the things to watch out for?


Secondly, which commercial support vendor would you recommend? I found 
EnterpriseDB and CommandPrompt, but I don't know anything about them. 
Any other candidates?


thanks,

Eugene




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] prepared statements and DBD::Pg

[Tim Bunce]

On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote:
> On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:
> > On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:
> > > On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:
> > > > On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:
> > > > > 
> > > > > WARNING: DBD::Pg now (as of version 1.40) uses true prepared
> > > > > statements by sending them to the backend to be prepared by
> > > > > the Postgres server.  Statements that were legal before may no
> > > > > longer work.
> > > > 
> > > > Sure seems like a bug, or at best a misfeature, that DBD::Pg
> > > > doesn't simply fallback to client-side prepare when a
> > > > server-side prepare can't be performed.  I believe DBD::mysql
> > > > does that.
> > > 
> > > It's a safety feature. :)
> > 
> > Er.  I see the smiley but I'm not sure if that's a joke.  Can you
> > expand?
> 
> It's not a joke.  Client-side prepare is essentially creating a
> duplicate code path and hoping that it does exactly the same thing
> that the server-side one does, and this in a context of controlling
> access.
> 
> If PostgreSQL's parser, etc., were in the form of exportable
> libraries, that would be very nice, but until then, making server-side
> prepare the only kind is just jungle caution.

So you're okay with breaking previously working, and prefectly valid, DBI code?

And you're okay with forcing application writers to "know" which kinds
of sql statements can, or can't, be server-side prepared by the
particular version of postgress they're talking to?

>From the DBI's perspective, $dbh->prepare($valid_sql_statement) should
always work.

Tim.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Column oriented pgsql

[Mag Gam]

Is it possible to tweak (easily) Postgresql so the storage is column
oriented versus row-oriented? We would like to increase read
optimization on our data which is about 2TB.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] A question about RAISE NOTICE

[Luigi N. Puleio]

Since I'm executing this from a PgSQL function, can be SQLWarning eventually 
called from inside the function?

and to use it let me guess I have to install something related to java like JRE 
1.5.0?... 

Thanks 
Ciao, Luigi

--- On Thu, 5/7/09, Kris Jurka  wrote:

From: Kris Jurka 
Subject: Re: [GENERAL] A question about RAISE NOTICE
To: "Craig Ringer" 
Cc: "Luigi N. Puleio" , pgsql-general@postgresql.org
Date: Thursday, May 7, 2009, 11:51 PM



On Thu, 7 May 2009, Craig Ringer wrote:

> Most programs will not be aware of notice messages. Unlike error
> messages there's no provision for them in standard JDBC/ODBC/etc APIs,
> and you have to specifically ask the client driver for them. Unless your
> app is aware of PostgreSQL specifically, it won't be doing that.

The JDBC API has a SQLWarning that we map notice messages to and are understood 
by client tools.

http://java.sun.com/javase/6/docs/api/java/sql/SQLWarning.html

Kris Jurka

-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



  

Re: [GENERAL] migrating from MSSQL

[Magnus Hagander]

Eugene . wrote:
> 
> Hi all,
> 
> I've been tasked with evaluating the feasibility of migrating our
> in-house application from MSSQL to PostgreSQL. It is fairly old and has
> lots of stored procedures, which is why we need to think carefully
> before making the switch. Does anyone else have a similar experience?
> What are some of the things to watch out for?

TSQL is very very different from any of the languages supported in
PostgreSQL. You are most likely going to end up having to rewrite all
the procedures more or less completely, unless they are just wrappers
around single SQL statements (in which case you can use a SQL language one).

One thing to note is that in PostgreSQL you can't just execute the
typical "sp_myfunc" command as you would in MSSQL - you need to do
"SELECT sp_myfunc()". That means you may need to change every call in
the application, if that's the syntax you have been using.


> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?

Depends completely on where in the world you are located. There is a
list of providers available on our website at
http://www.postgresql.org/support/professional_support

The ones you recommended are both good, but depending on where you are
there may be another good option available closer to you.

//Magnus

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Accessing pg_controldata information from SQL

[Massa, Harald Armin]

Hello,

is there any way to acess the pg_controldata information via SQL?

(running pg_controldata via shell needs file access to the postgresql data
dictionary, which is usually not given)

Harald

-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

Re: [GENERAL] Question about function returning record

[Merlin Moncure]

On Thu, May 7, 2009 at 7:37 AM, Markus Wollny  wrote:
> Hi!
>
> I've got a generalized function
>
>        getshadowrecord(schema (varchar),table (varchar), id (int4),
> version_id (int))
>
> which returns RECORD. As this function is supposed to work on any table
> structure, I need to declare a column alias list when I do a SELECT on
> it, like
>
>        SELECT *
>        FROM getshadowrecord('foo','article',683503,0) AS shadowrecord (
>
>        id integer,
>        type_id integer ,
>        headline text,
>        strapline text,
>        [...]
>        );
>
> Now I'd like to make things easier for my developers by supplying sort
> of alias functions for each table like
>
>        CREATE or REPLACE FUNCTION "foo"."getshadow_article"(
>          IN "_id" int4,
>          IN "_versid" int4)
>        RETURNS foo.article_shadow AS
>        $BODY$
>                SELECT *
>                FROM getshadowrecord('foo','article',$1,$2) AS
> shadowrecord (
>                id integer,
>                type_id integer ,
>                headline text,
>                strapline text,
>                [...]
>                );
>        $BODY$
>        LANGUAGE SQL VOLATILE;
>
> Using these alias functions, they can simply do a SELECT * FROM
> foo.getshadow_article(id,version_id) without having to write the column
> list.
>
> As each of those alias functions would correspond exactly to one table,
> I wonder if there is a more elegant alternative to explicitly declaring
> the column list, something like this:
>
>        CREATE or REPLACE FUNCTION "foo"."getshadow_article"(
>          IN "_id" int4,
>          IN "_versid" int4)
>        RETURNS foo.article_shadow AS
>        $BODY$
>                SELECT *
>                FROM getshadowrecord('foo','article',$1,$2) AS
> shadowrecord (foo.article_shadow%rowtype);
>        $BODY$
>        LANGUAGE SQL VOLATILE;
>
> Unfortunately my example doesn't work, but I think you'll know what I'd
> like to do. The only way I see to solve this so far, would be to use
> pl/pgsql or pl/perl, issue a query to the information_schema.columns
> table, then assemble the query string with the column list and execute
> that. I'd like to know if there's some better way to implement this,
> something that would somehow use the %rowtype construct.
> Kind regards
>

what version postgres?

In recent versions (I think 8.3), you can have a function return
''text' like this:

create function  bar_or_foo(is_bar bool) returns text as
$$
  select case when is_bar then bar::text else foo::text end;
$$ language sql;

select bar_or_foo(true)::bar;

select bar_or_foo(false)::foo;

select (bar).* from (select bar_or_foo(true)::bar as bar);

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[Simon Riggs]

On Fri, 2009-05-08 at 00:08 -0700, Eugene . wrote:

> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?

Those two companies operate mostly in US. There are various other
companies in Europe, South America, Asia, etc who may be able to provide
better alternatives. Major contributors are spread across the world, not
localised in any country, timezone or language (though English is the
accepted language on this particular list).

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[johnf]

On Friday 08 May 2009 12:08:44 am Eugene . wrote:
> Hi all,
>
> I've been tasked with evaluating the feasibility of migrating our in-house
> application from MSSQL to PostgreSQL. It is fairly old and has lots of
> stored procedures, which is why we need to think carefully before making
> the switch. Does anyone else have a similar experience? What are some of
> the things to watch out for?
>
> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them. Any
> other candidates?
>
> thanks,
>
> Eugene

I know this is going to sound like pie in the sky but.  I had the same task.  
I found a program that converted everything on the web.  However, the program 
was very expensive over $3000.00 US.  The author provided a test demo that 
would convert 2 or 3 SP's.  It did look very good.  The author is more 
interested in getting large companies to make purchases.  Not that is a bad 
thing only that he was not to responsive to a non-profit request.  Keep 
looking because the name escapes me and also the link.

-- 
John Fabiani

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Remote access

[George Weaver]

Hi,

I have a client with a main office and a branch office about 90 miles away.

They have a server at the main office but it is not a web server.

What would be the best solution for them to access a PostgreSQL database 
located at the main office from the branch office?

I am not "network savvy", but if I get pointed in the right direction I should 
be able to fill in the blanks.

Thanks in advance!

George

Re: [GENERAL] migrating from MSSQL

[Bill Moran]

In response to johnf :

> On Friday 08 May 2009 12:08:44 am Eugene . wrote:
> > Hi all,
> >
> > I've been tasked with evaluating the feasibility of migrating our in-house
> > application from MSSQL to PostgreSQL. It is fairly old and has lots of
> > stored procedures, which is why we need to think carefully before making
> > the switch. Does anyone else have a similar experience? What are some of
> > the things to watch out for?
> >
> > Secondly, which commercial support vendor would you recommend? I found
> > EnterpriseDB and CommandPrompt, but I don't know anything about them. Any
> > other candidates?

I took a project to do this under contract a few years ago.  Quite
frankly, it was an interesting and trouble-free project.  Any
competent programmer should be able to handle it for you.  The cost
involved is going to depend on the amount of procedures to convert.

Of course, I did it all by hand.  If someone out there has a program
to convert, it would probably reduce the cost.  But I also had the
ability to optimize some of the SPs as I converted them.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[A. Kretschmer]

In response to George Weaver :
> Hi,
>  
> I have a client with a main office and a branch office about 90 miles away.
>  
> They have a server at the main office but it is not a web server.
>  
> What would be the best solution for them to access a PostgreSQL database
> located at the main office from the branch office?
>  
> I am not "network savvy", but if I get pointed in the right direction I should
> be able to fill in the blanks.

There are some ways to do that, for instance a so called SSH-Tunnel,
read http://docs.planetargon.com/PostgreSQL_SSH_Tunnel

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] prepared statements and DBD::Pg

[Daniel Verite]

Tim Bunce wrote:

So you're okay with breaking previously working, and prefectly valid, 

DBI code?

I think the rationale is that such code was working by virtue of how 
prepare() was implemented in DBD::Pg, but was not "valid" nonetheless, 
as outlined with this example:

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

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[George Weaver]

From: "A. Kretschmer"



In response to George Weaver :

Hi,

I have a client with a main office and a branch office about 90 miles 
away.


They have a server at the main office but it is not a web server.

What would be the best solution for them to access a PostgreSQL database
located at the main office from the branch office?

I am not "network savvy", but if I get pointed in the right direction I 
should

be able to fill in the blanks.


There are some ways to do that, for instance a so called SSH-Tunnel,
read http://docs.planetargon.com/PostgreSQL_SSH_Tunnel


Hi Andreas,

Thanks for the link!

(Pardon my ignorance) but does this require that the server be a web server 
with a fixed IP address?


George





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[Andrew Gould]

On Fri, May 8, 2009 at 9:07 AM, George Weaver - Cleartag Software <
gwea...@cleartagsoftware.com> wrote:

>
> Hi Andrew,
>
>  Hi,
>>>
>>
>  I have a client with a main office and a branch office about 90 miles
>>> away.
>>>
>>
>  They have a server at the main office but it is not a web server.
>>>
>>
>  What would be the best solution for them to access a PostgreSQL database
>>> located at the main office from the branch office?
>>>
>>
>  I am not "network savvy", but if I get pointed in the right direction I
>>> should be able to fill in the blanks.
>>>
>>
>  Thanks in advance!
>>>
>>
>  The best solution will depend upon the type of activity the branch office
>> needs to perform with the database server.
>>
>
> The application is an inventory management system.  Their current solution
> is an MS Access based system and they use Microsoft Terminal Services to
> enable the branch office to access the main office server.  They are
> considering a move to an application that I provide which utilizes
> PostgreSQL.
>
>  Also, what operating system(s) are they running at the branch office?
>>
>
> Everything is Windows based.
>
> George
>
>
Hi George,

MS Access can access PostgreSQL servers via ODBC links.  There is an SSL
Mode configuration option in the PostgreSQL ODBC driver for security.

Best of luck,

Andrew

Re: [GENERAL] prepared statements and DBD::Pg

[David Fetter]

On Fri, May 08, 2009 at 09:44:56AM +0100, Tim Bunce wrote:
> On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote:
> > On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:
> > > On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:
> > > > On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:
> > > > > On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:
> > > > > > 
> > > > > > WARNING: DBD::Pg now (as of version 1.40) uses true
> > > > > > prepared statements by sending them to the backend to be
> > > > > > prepared by the Postgres server.  Statements that were
> > > > > > legal before may no longer work.
> > > > > 
> > > > > Sure seems like a bug, or at best a misfeature, that DBD::Pg
> > > > > doesn't simply fallback to client-side prepare when a
> > > > > server-side prepare can't be performed.  I believe
> > > > > DBD::mysql does that.
> > > > 
> > > > It's a safety feature. :)
> > > 
> > > Er.  I see the smiley but I'm not sure if that's a joke.  Can
> > > you expand?
> > 
> > It's not a joke.  Client-side prepare is essentially creating a
> > duplicate code path and hoping that it does exactly the same thing
> > that the server-side one does, and this in a context of
> > controlling access.
> > 
> > If PostgreSQL's parser, etc., were in the form of exportable
> > libraries, that would be very nice, but until then, making
> > server-side prepare the only kind is just jungle caution.
> 
> So you're okay with breaking previously working, and prefectly
> valid, DBI code?

That's not the kind of code it broke.

> And you're okay with forcing application writers to "know" which
> kinds of sql statements can, or can't, be server-side prepared by
> the particular version of postgress they're talking to?

They need to know what kinds of SQL statements are valid, full stop.

> From the DBI's perspective, $dbh->prepare($valid_sql_statement)
> should always work.

Yes, it should, and unless and until PostgreSQL's parser becomes an
exportable library, there will be no way to establish that on the
client side.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[Craig Ringer]

A. Kretschmer wrote:

> There are some ways to do that, for instance a so called SSH-Tunnel,
> read http://docs.planetargon.com/PostgreSQL_SSH_Tunnel

SSH tunneling transports TCP over a TCP encapsulation. The adaptive rate
control may not work how you'd expect; I've had some odd issues with SSH
tunnels in the past that've turned out to be caused by TCP rate control
issues.

In any case, a MUCH better option in almost all cases will be SSL,
possibly with client certificates.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[George Weaver]

Andrew Gould  in reponse to George Weaver wrote:


Hi Andrew,



Hi,



I have a client with a main office and a branch office about 90 miles
away.



They have a server at the main office but it is not a web server.



What would be the best solution for them to access a PostgreSQL database
located at the main office from the branch office?



I am not "network savvy", but if I get pointed in the right direction I
should be able to fill in the blanks.



Thanks in advance!



The best solution will depend upon the type of activity the branch office

needs to perform with the database server.


The application is an inventory management system.  Their current solution

is an MS Access based system and they use Microsoft Terminal Services to
enable the branch office to access the main office server.  They are
considering a move to an application that I provide which utilizes
PostgreSQL.


Also, what operating system(s) are they running at the branch office?



Everything is Windows based.



George



Hi George,



MS Access can access PostgreSQL servers via ODBC links.  There is an SSL
Mode configuration option in the PostgreSQL ODBC driver for security.


Hi Andrew,

I have clients with web-based servers which utilize my application 
connecting via the internet.  I guess where my ignorance manifests itself is 
how to connect when the server is not a web server and doesn't have a fixed 
IP address.  Is it necessary for them to set the server up with a fixed 
address, or is there some other alternative?


Thanks for your time.

George



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "No transaction in progress" warning

[Conrad Lender]

On 07/05/09 18:26, Tom Lane wrote:
> Scott Mead  writes:
>> How did you restart postgres?  I'm guessing that you're using a
>> distribution provided package.  If you're using the /etc/init.d
>> scripts from that package, it's likely that the startup script is
>> redirecting stderr and that the system is configured to use syslog
>> for the rest of the logging.
> 
> Right.  There are a number of messages that can appear at startup
> before the postmaster reads and adopts the "where to log" settings in
>  postgresql.conf.  Those early messages are going to go to
> postmaster's stderr.  What it sounds like is you're using a start
> script that points postmaster stderr to
> /var/log/postgresql/postgresql-8.3-main.log. I don't find that to be
> a tremendously good idea --- in my RPMs the early-startup messages go
> to a fixed file (/var/lib/pgsql/pgstartup.log) that's not dependent
> on what the "where to log" configuration settings are.

I looked into the startup scripts that are used here (Ubuntu 8.10): the
/etc/init.d script calls a Perl script and redirects that command's
stderr to stdin to capture warnings and errors:

ERRMSG=$(pg_ctlcluster 8.3 main start 2>&1)

The pg_ctlcluster script parses postgresql.conf and, if log_filename and
log_directory aren't defined there, adds '-l
/var/log/postgresql/postgresql-8.3-main.log' as an option for pg_ctl.
This file is created with the appropriate permissions if necessary.
The script then forks, and the child detaches itself from the terminal
and redirects stdout and stderr to /dev/null:

  setsid;
  dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 1);
  dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 2);
  exec $pg_ctl @options;

That doesn't look too bad to me, or at least it's how I would write a
daemon script, too. If I understand you correctly, stderr should be left
intact?

If this is not the preferred way to handle logging with Postgres, maybe
I should ask pg_ctlcluster's maintainer about it.


Regards,
  - Conrad

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[Bill Moran]

In response to "George Weaver" :

[snip]
 
> I have clients with web-based servers which utilize my application 
> connecting via the internet.  I guess where my ignorance manifests itself is 
> how to connect when the server is not a web server and doesn't have a fixed 
> IP address.  Is it necessary for them to set the server up with a fixed 
> address, or is there some other alternative?

There are alternatives to a fixed IP ... all of them are Very Bad
Ideas, and generally unreliable.  If you ask around on enough message
boards, you will have people recommending all sorts of hacks where the
server periodically reports it's IP to a DNS server that updates its
records.

If you're trying to put together something that will be reliable and
professional, don't do any of those -- just spend the extra money
to get a static IP.  In fact, take a serious look at having that
server colocated at a facility with 24/7 monitoring, and redundant
power and redundant ethernet.  There are many places that will
proved you a static IP and rent you rack space for ~$150/month.
That kind of thing will provide you with the professional reliability
that most people expect but will never get from consumer DSL and
cable connections.  If you're budget is so tight that $150/month
is too much, then you'll have to fall back on one of the "magically
keep my DNS updated" solutions that others can surely give you
details on, and my heart will weep for your pain.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[Joshua D. Drake]

On Fri, 2009-05-08 at 10:11 +0200, Magnus Hagander wrote:
> Eugene . wrote:

> > Secondly, which commercial support vendor would you recommend? I found
> > EnterpriseDB and CommandPrompt, but I don't know anything about them.
> > Any other candidates?
> 

As a person from Command Prompt :) we do have people on staff with
experience with this. I am sure that EnterpriseDB does as well.


> Depends completely on where in the world you are located. There is a
> list of providers available on our website at
> http://www.postgresql.org/support/professional_support

Although proximity can be important he should work with the company that
suits his needs as a whole. There a number of long standing companies
that would likely do a good job for him:

OmniTI : http://www.omniti.com
Command Prompt : http://www.commandprompt.com/
EnterpriseDB : http://www.enterprisedb.com/
Redpill : http://www.redpill-linpro.com/
2nd Quandrant : http://www.2ndquandrant.com/

Sincerely,

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[Joshua D. Drake]

On Fri, 2009-05-08 at 10:45 -0400, Bill Moran wrote:
> In response to "George Weaver" :

> proved you a static IP and rent you rack space for ~$150/month.
> That kind of thing will provide you with the professional reliability
> that most people expect but will never get from consumer DSL and
> cable connections.  If you're budget is so tight that $150/month
> is too much, then you'll have to fall back on one of the "magically
> keep my DNS updated" solutions that others can surely give you
> details on, and my heart will weep for your pain.

Or get a VPS... for 40.00 bucks a month.

Joshua D. Drkae


> 
> -- 
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
> 
-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[Johan Nel]

George Weaver wrote:

Hi,
 
I have a client with a main office and a branch office about 90 miles away.
 
They have a server at the main office but it is not a web server.
 
What would be the best solution for them to access a PostgreSQL database 
located at the main office from the branch office?
 
I am not "network savvy", but if I get pointed in the right direction I 
should be able to fill in the blanks.
 
Thanks in advance!
 
George


You mention "network savvy" so I will assume the branch office is 
connected via a wide area network to the main office.


In that case:
You probably don't need to do anything except making sure that the 
server and PG allows access from the IP address range used at the branch 
office.


From the branch office you need to make your connection point to the IP 
address of the PG server...


HTH,

Johan Nel
Pretoria, South Africa.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[George Weaver - Cleartag Software]

Hi Andrew,


Hi,


I have a client with a main office and a branch office about 90 miles 
away.



They have a server at the main office but it is not a web server.


What would be the best solution for them to access a PostgreSQL database 
located at the main office from the branch office?


I am not "network savvy", but if I get pointed in the right direction I 
should be able to fill in the blanks.



Thanks in advance!



The best solution will depend upon the type of activity the branch office
needs to perform with the database server.


The application is an inventory management system.  Their current solution
is an MS Access based system and they use Microsoft Terminal Services to
enable the branch office to access the main office server.  They are
considering a move to an application that I provide which utilizes
PostgreSQL.


Also, what operating system(s) are they running at the branch office?


Everything is Windows based.

George


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] structure of query does not match error during RETURN QUERY.

[Michal Szymanski]

Hi,
We call DB procedure that select rows with given ID it works as simple
SELECT but for future changes we implement as DB procedure (look below
for DB listing). Recently we modified columns in table
cerber.cerber_accoun and after this modification procedure does not
work anymore and it returns
42804: structure of query does not match
It is very strange because we return row of cerber_account in variable
defined as row of cerber_account.
We have tried to restart database but it does not help. Probably
information about old table structure is somewhere cached.

CREATE OR REPLACE FUNCTION cerber.cerber_account_select
(i_cerber_account_id bigint)
  RETURNS SETOF cerber.cerber_account AS
$BODY$
DECLARE
v_result cerber.cerber_account%ROWTYPE;
BEGIN
RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
cerber_account_id=i_cerber_account_id;
RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


Michal Szymankis
http://blog.szymanskich.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] structure of query does not match error during RETURN QUERY.

[Michal Szymanski]

We use Postgres 8.3.7 on Linux

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[Andrew Gould]

On Fri, May 8, 2009 at 9:37 AM, George Weaver  wrote:

> Andrew Gould  in reponse to George Weaver wrote:
>
>  Hi Andrew,
>>>
>>
>  Hi,
>

>  I have a client with a main office and a branch office about 90 miles
> away.
>

>  They have a server at the main office but it is not a web server.
>

>  What would be the best solution for them to access a PostgreSQL database
> located at the main office from the branch office?
>

>  I am not "network savvy", but if I get pointed in the right direction I
> should be able to fill in the blanks.
>

>  Thanks in advance!
>

>  The best solution will depend upon the type of activity the branch office

>>> needs to perform with the database server.
>
>  The application is an inventory management system.  Their current solution
>>>
>> is an MS Access based system and they use Microsoft Terminal Services to
> enable the branch office to access the main office server.  They are
> considering a move to an application that I provide which utilizes
> PostgreSQL.
>
>  Also, what operating system(s) are they running at the branch office?

>>>
>  Everything is Windows based.
>>>
>>
>  George
>>>
>>
>  Hi George,
>>
>
>  MS Access can access PostgreSQL servers via ODBC links.  There is an SSL
>> Mode configuration option in the PostgreSQL ODBC driver for security.
>>
>
> Hi Andrew,
>
> I have clients with web-based servers which utilize my application
> connecting via the internet.  I guess where my ignorance manifests itself is
> how to connect when the server is not a web server and doesn't have a fixed
> IP address.  Is it necessary for them to set the server up with a fixed
> address, or is there some other alternative?
>
> Thanks for your time.
>
> George
>

You should either get a static IP address or use a service that maps your
changing IP address to a server name.

I use DynDNS.com and a perl application called ddclient.  DynDNS manages my
domain name in their DNS.  ddclient monitors my home internet IP address and
sends an update to DynDNS automatically whenever the IP address changes.
When I try to access my domain name, the domain name is mapped to my home IP
address and my home router forwards the allowed ports to the appropriate
computer.

DynDNS is not the only provider of this kind of service.  ddclient is not
the only (free) application that performs this function.  They have worked
for me; but there are many options available to you.

Andrew

Re: [GENERAL] "No transaction in progress" warning

[Conrad Lender]

On 08/05/09 16:43, Conrad Lender wrote:
> stderr to stdin to capture warnings and errors:

That should be "stderr to stdout", of course.

  - Conrad

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[Dave Page]

On Fri, May 8, 2009 at 3:48 PM, Joshua D. Drake  wrote:
> On Fri, 2009-05-08 at 10:11 +0200, Magnus Hagander wrote:
>> Eugene . wrote:
>
>> > Secondly, which commercial support vendor would you recommend? I found
>> > EnterpriseDB and CommandPrompt, but I don't know anything about them.
>> > Any other candidates?
>>
>
> As a person from Command Prompt :) we do have people on staff with
> experience with this. I am sure that EnterpriseDB does as well.

Compatibility is something of a specialty of ours as you know, and we
are experienced with DBMSs other than Oracle, including SQL Server.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "No transaction in progress" warning

[Tom Lane]

Conrad Lender  writes:
> I looked into the startup scripts that are used here (Ubuntu 8.10):
> ...
> The pg_ctlcluster script parses postgresql.conf and, if log_filename and
> log_directory aren't defined there, adds '-l
> /var/log/postgresql/postgresql-8.3-main.log' as an option for pg_ctl.
> This file is created with the appropriate permissions if necessary.
> The script then forks, and the child detaches itself from the terminal
> and redirects stdout and stderr to /dev/null:

>   setsid;
>   dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 1);
>   dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 2);
>   exec $pg_ctl @options;

This seems like a whole lot of work to do mostly the wrong thing.
I think the Ubuntu maintainer has failed to think carefully about
what should happen to startup-time messages --- what if pg_ctl itself
fails and would like to say something about that?  Good luck finding
out about that from /dev/null.  What about postmaster messages that are
emitted before the postgresql.conf logging options have taken effect?
If there are any such options, early messages will end up in /dev/null
too because the -l switch isn't supplied.

It's a lot simpler and safer to just unconditionally send stderr to
someplace reliable throughout the startup process.  Now you don't
really want that once the postmaster is up and running, because there's
no provision for log rotation on plain stderr output --- but that's
what the postgresql.conf logging options are for.  But trying to be
fancy about what happens before they've kicked in isn't very productive,
and in this case seems downright counterproductive.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] prepared statements and DBD::Pg

[Tim Bunce]

On Fri, May 08, 2009 at 04:02:29PM +0200, Daniel Verite wrote:
>   Tim Bunce wrote:
>
>> So you're okay with breaking previously working, and prefectly valid, 
> DBI code?
>
> I think the rationale is that such code was working by virtue of how 
> prepare() was implemented in DBD::Pg, but was not "valid" nonetheless, as 
> outlined with this example:
> http://archives.postgresql.org/pgsql-general/2005-11/msg00339.php

It's perfectly valid (from the DBI's point of view) for prepare() to
return a prepared statement handle for an invalid statement.

It's not the job of prepare() to validate the SQL. It's a bonus if it
does, but the primary goal is "to prepare as much as possible" for
future execution.

There are *many* DBI drivers that can't/don't validate the SQL on
prepare. DBD::Oracle, for example, can but doesn't by default.
It defers the prepare until the first execute (or meta data is
requested) in order to reduce the number of round-trips.


The example that started this thread was that this valid statement
worked:

prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT INTO foo(2, 
2);")

but this valid statement didn't:

prepare("INSERT INTO foo(1, 1); INSERT INTO foo(2, 
2);")

My argument is that both calls should return statement handles.

The DBI user should not be exposed to the inner-workings and limitations
of the support for server-side prepare.

If a server-side prepare is attempted and fails because it's a kind of
statement that can't be server-side prepared then DBD::pg should
fallback to a client-side prepare. It does not matter that this may mean
some invalid statements are caught by prepare() and others by execute().
The DBI spec has always allowed for that.

Tim.

p.s. I'd be happy to see 'success with info' status returned if the
prepare() has to unexpectly fallback to client-side (and perhaps a dbh
counter incremeted). So users can tell when and how often it's happening
if they want to.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] migrating from MSSQL

[David Fetter]

On Fri, May 08, 2009 at 12:21:51AM -0700, Eugene . wrote:
> 
> Hi all,
> 
> I've been tasked with evaluating the feasibility of migrating our
> in-house application from MSSQL to PostgreSQL.  It is fairly old and
> has lots of stored procedures, which is why we need to think
> carefully before making the switch.  Does anyone else have a similar
> experience?

Yes.

> What are some of the things to watch out for?

Porting the T-SQL code is one part.  Differing transaction semantics
is another.

> Secondly, which commercial support vendor would you recommend?  I
> found EnterpriseDB and CommandPrompt, but I don't know anything
> about them.  Any other candidates?

Among many others, there's also OmniTI  and my
company, PostgreSQL Experts .

One of the advantages of a free software project like PostgreSQL is
that you can choose the support option that best fits your needs
rather than being tied to one company whose support options may not.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how many connections can i use????

[Edmundo Robles L.]

Hello there!

I have a problem with the number of connections on postgres 7.2.1, yeah
is  a   quite older :-) 
but  i have it  running on SCO OpenServer 5.0.7 and  tried to change
the number of connections  above  100 but,
  i only got 100  sometimes  101 but no more.

I changed  the  parameters line   of postmaster to  "-N 128 -B 256" , "
-N 256  -B 512", etc;  even  modified  the max_connections and
shared_buffers on  postgresql.conf but  i  only got 100 connections :-(


I hope  the following info, could help to solve the above mentioned:

Operative system: SCO OpenServer 5.0.7

SHMMAX=106340761
SHMMIN= 1
SHMALL=512
SHMSEG=6
SHMMNI=100
SEMMNI= 256
SEMMSL=150
SEMMAP=8192
SEMVMX=32767


memsize: 1063407616

thanks in advance, and regards from  Mexico.
--
Di  NO  a la pirateria, usa software libre.
Say NO  to  piracy,   use  free(dom) software.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[George Weaver]

Hi Andrew,

Interesting!  Thanks for the detail.

George
  - Original Message - 
  From: Andrew Gould 
  To: George Weaver 
  Cc: pgsql-general 
  Sent: Friday, May 08, 2009 9:57 AM
  Subject: Re: [GENERAL] Remote access


  On Fri, May 8, 2009 at 9:37 AM, George Weaver  wrote:

Andrew Gould  in reponse to George Weaver wrote:


Hi Andrew,



Hi,



I have a client with a main office and a branch office about 90 
miles
away.



They have a server at the main office but it is not a web server.



What would be the best solution for them to access a PostgreSQL 
database
located at the main office from the branch office?



I am not "network savvy", but if I get pointed in the right 
direction I
should be able to fill in the blanks.



Thanks in advance!



  The best solution will depend upon the type of activity the branch 
office

needs to perform with the database server.


The application is an inventory management system.  Their current 
solution

is an MS Access based system and they use Microsoft Terminal Services to
enable the branch office to access the main office server.  They are
considering a move to an application that I provide which utilizes
PostgreSQL.


  Also, what operating system(s) are they running at the branch office?



Everything is Windows based.



George



  Hi George,



  MS Access can access PostgreSQL servers via ODBC links.  There is an SSL
  Mode configuration option in the PostgreSQL ODBC driver for security.



Hi Andrew,

I have clients with web-based servers which utilize my application 
connecting via the internet.  I guess where my ignorance manifests itself is 
how to connect when the server is not a web server and doesn't have a fixed IP 
address.  Is it necessary for them to set the server up with a fixed address, 
or is there some other alternative?

Thanks for your time.

George


  You should either get a static IP address or use a service that maps your 
changing IP address to a server name.

  I use DynDNS.com and a perl application called ddclient.  DynDNS manages my 
domain name in their DNS.  ddclient monitors my home internet IP address and 
sends an update to DynDNS automatically whenever the IP address changes.  When 
I try to access my domain name, the domain name is mapped to my home IP address 
and my home router forwards the allowed ports to the appropriate computer.

  DynDNS is not the only provider of this kind of service.  ddclient is not the 
only (free) application that performs this function.  They have worked for me; 
but there are many options available to you.

  Andrew

Re: [GENERAL] Remote access

[George Weaver]

- Original Message - 
From: "Bill Moran" 

To: "George Weaver" 
Cc: "pgsql-general" 
Sent: Friday, May 08, 2009 9:45 AM
Subject: Re: [GENERAL] Remote access



In response to "George Weaver" :

[snip]


I have clients with web-based servers which utilize my application
connecting via the internet.  I guess where my ignorance manifests itself 
is
how to connect when the server is not a web server and doesn't have a 
fixed

IP address.  Is it necessary for them to set the server up with a fixed
address, or is there some other alternative?


There are alternatives to a fixed IP ... all of them are Very Bad
Ideas, and generally unreliable.  If you ask around on enough message
boards, you will have people recommending all sorts of hacks where the
server periodically reports it's IP to a DNS server that updates its
records.

If you're trying to put together something that will be reliable and
professional, don't do any of those -- just spend the extra money
to get a static IP.  In fact, take a serious look at having that
server colocated at a facility with 24/7 monitoring, and redundant
power and redundant ethernet.  There are many places that will
proved you a static IP and rent you rack space for ~$150/month.
That kind of thing will provide you with the professional reliability
that most people expect but will never get from consumer DSL and
cable connections.  If you're budget is so tight that $150/month
is too much, then you'll have to fall back on one of the "magically
keep my DNS updated" solutions that others can surely give you
details on, and my heart will weep for your pain.


Good info.  I want to avoid pain so will suggest to the client that they 
seriously consider this approach.


Thanks Bill. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] structure of query does not match error during RETURN QUERY.

[Gevik Babakhani]

How do you call this function from your code?

Does it work when you call it from PGAdmin?

select  * from cerber.cerber_account_select (1);

Michal Szymanski wrote:

Hi,
We call DB procedure that select rows with given ID it works as simple
SELECT but for future changes we implement as DB procedure (look below
for DB listing). Recently we modified columns in table
cerber.cerber_accoun and after this modification procedure does not
work anymore and it returns
42804: structure of query does not match
It is very strange because we return row of cerber_account in variable
defined as row of cerber_account.
We have tried to restart database but it does not help. Probably
information about old table structure is somewhere cached.

CREATE OR REPLACE FUNCTION cerber.cerber_account_select
(i_cerber_account_id bigint)
  RETURNS SETOF cerber.cerber_account AS
$BODY$
DECLARE
v_result cerber.cerber_account%ROWTYPE;
BEGIN
RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
cerber_account_id=i_cerber_account_id;
RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


Michal Szymankis
http://blog.szymanskich.net

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how many connections can i use????

[Tom Lane]

"Edmundo Robles L."  writes:
> I have a problem with the number of connections on postgres 7.2.1,

egad ... the number of known bugs in that would curl your toes.
Think about an upgrade.  Do more than just think about it.

> I changed  the  parameters line   of postmaster to  "-N 128 -B 256" , "
> -N 256  -B 512", etc;  even  modified  the max_connections and
> shared_buffers on  postgresql.conf but  i  only got 100 connections :-(

You sure you restarted the postmaster?

> Operative system: SCO OpenServer 5.0.7

double egad (and a bit at odds with your sig...)

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

FW: [GENERAL] how many connections can i use????

[Edmundo Robles L.]

 
"Edmundo Robles L."  writes:
> I have a problem with the number of connections on postgres 7.2.1,

-->egad ... the number of known bugs in that would curl your toes.
-->Think about an upgrade.  Do more than just think about it.

yes,  maybe  the  upgrade solve that problem, i will try 
with another version and test it.




> I changed  the  parameters line   of postmaster to  "-N 128 -B 256" , "
> -N 256  -B 512", etc;  even  modified  the max_connections and
> shared_buffers on  postgresql.conf but  i  only got 100 connections :-(

-->You sure you restarted the postmaster?

Yes i sure!, each time i modified the  parameter i restart the server :-)

> Operative system: SCO OpenServer 5.0.7

--> double egad (and a bit at odds with your sig...)

jeje  :-) yes , this is because in my work   we use SCO but personally i  use 
ubuntu on my desktop pc and laptop.

regards, tom lane



--
Di NO a la pirateria, usa software libre.
Say NO to piracy, use free(dom) software.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] structure of query does not match error during RETURN QUERY.

[Gevik Babakhani]

I see that now... I guess this is bug. please see:
http://archives.postgresql.org//pgsql-bugs/2008-11/msg00172.php

If it is possible for you, dump and restore the database.

I hope this helps.


Michal Szymanski wrote:
We have tried to call it from PHP and from pgadmin the result is 
always the same.

This is result from psql

crm_test=# select  * from cerber.cerber_account_select (1);
WARNING:  :ERROR:CERBER:cerber_account_select: Blad typu other w 
trakcie probu pobrania 
danych. Kod bledu = [42804: structure of query does not match function 
result type]
 cerber_account_id | user_name | password | status | last_login_date | 
creation_date | 
modification_date | delete_date | id_sys_module | id_domain
---+---+--++-+--
-+---+-+---+---

(0 rows)


Gevik Babakhani wrote:

How do you call this function from your code?

Does it work when you call it from PGAdmin?

select  * from cerber.cerber_account_select (1);

Michal Szymanski wrote:

Hi,
We call DB procedure that select rows with given ID it works as simple
SELECT but for future changes we implement as DB procedure (look below
for DB listing). Recently we modified columns in table
cerber.cerber_accoun and after this modification procedure does not
work anymore and it returns
42804: structure of query does not match
It is very strange because we return row of cerber_account in variable
defined as row of cerber_account.
We have tried to restart database but it does not help. Probably
information about old table structure is somewhere cached.

CREATE OR REPLACE FUNCTION cerber.cerber_account_select
(i_cerber_account_id bigint)
  RETURNS SETOF cerber.cerber_account AS
$BODY$
DECLARE
v_result cerber.cerber_account%ROWTYPE;
BEGIN
RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
cerber_account_id=i_cerber_account_id;
RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


Michal Szymankis
http://blog.szymanskich.net

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.0.238 / Virus Database: 270.12.21/2103 - Release Date: 05/07/09 18:05:00


  





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Glitch Using a Rule to Copy Data to a Table

[gwlucas]

I have a glitch using a rule to copy data to a table. I was wondering if
anyone could clarify why the following doesn’t work and suggest to me an
alternate way of accomplishing my objective… 

I have a tables called (for sake of the example) “bravo” and “charlie”, and
I want to use a rule to automatically copy data from bravo to charlie when a
new record is inserted into bravo.  I declare a rule and all seems to work
fine until I try to impose some restrictions on what goes into bravo…   

In the example that follows, the table “alpha” is a source of data...  It
contains only one attribute which is called “name”.  I want to insert
records into bravo using a select from alpha… with the further restriction
that if I’ve already got a record in bravo with that name, it doesn’t get
added to the table a second time. This all sounds kind of arbitrary, but it
is based on a real-world application for which this is a reasonable
restriction.  The crux of the problem isn’t the selection, but the copy
operation.

So, in the example below, I perform two queries, the first inserts data into
bravo with a successful copy to charlie.  The second inserts data into
bravo, but does not copy it.  


CREATE TABLE alpha   (name VARCHAR(32));
CREATE TABLE bravo   (name VARCHAR(32), flavor VARCHAR(32));
CREATE TABLE charlie (name VARCHAR(32), flavor VARCHAR(32));

INSERT INTO  alpha(name) VALUES('Liz' );
INSERT INTO  alpha(name) VALUES('Jay');
INSERT INTO  alpha(name) VALUES('Bill');

CREATE rule charlie_copy_rule AS 
ON INSERT TO bravo DO
INSERT INTO charlie VALUES(NEW.name, NEW.flavor);

--- a simple insert into bravo using the rule to make
--- a copy into charlie.  This works fine
INSERT INTO bravo(name, flavor)
   (SELECT name, 'Chocolate' FROM alpha WHERE NAME='Liz');

--- now insert into bravo only those entries that do not 
--- already exist.  This isn's so fine

INSERT INTO bravo (SELECT name , 'Vanilla' FROM alpha
WHERE NOT EXISTS(SELECT 1 FROM bravo where bravo.name=alpha.name));

SELECT * FROM bravo;
SELECT * FROM charlie;

The results from bravo are just what I'd expect
name |  flavor   
--+---
 Liz  | Chocolate
 Jay  | Vanilla
 Bill | Vanilla
(3 rows)

But the results from charlie don't include the results from the second
insert.

 name |  flavor   
--+---
 Liz  | Chocolate
(1 row)


So I am left wondering why the records from the second query didn’t make it
into the table named charlie.  Any thoughts?

Thanks in advance for your help.

Gary



Computer Programming is the Art of the Possible
 
Gary Lucas, Software Engineer
Sonalysts, Inc
215 Parkway North
Waterford, CT 06385




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] structure of query does not match error during RETURN QUERY.

[Michal Szymanski]

We have tried to call it from PHP and from pgadmin the result is always 
the same.

This is result from psql

crm_test=# select  * from cerber.cerber_account_select (1);
WARNING:  :ERROR:CERBER:cerber_account_select: Blad typu other w trakcie 
probu pobrania danych. Kod 
bledu = [42804: structure of query does not match function 
result type]
cerber_account_id | user_name | password | status | last_login_date | 
creation_date | 
modification_date | delete_date | id_sys_module | id_domain
---+---+--++-+--
-+---+-+---+---

(0 rows)


Gevik Babakhani wrote:

How do you call this function from your code?

Does it work when you call it from PGAdmin?

select  * from cerber.cerber_account_select (1);

Michal Szymanski wrote:

Hi,
We call DB procedure that select rows with given ID it works as simple
SELECT but for future changes we implement as DB procedure (look below
for DB listing). Recently we modified columns in table
cerber.cerber_accoun and after this modification procedure does not
work anymore and it returns
42804: structure of query does not match
It is very strange because we return row of cerber_account in variable
defined as row of cerber_account.
We have tried to restart database but it does not help. Probably
information about old table structure is somewhere cached.

CREATE OR REPLACE FUNCTION cerber.cerber_account_select
(i_cerber_account_id bigint)
  RETURNS SETOF cerber.cerber_account AS
$BODY$
DECLARE
v_result cerber.cerber_account%ROWTYPE;
BEGIN
RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
cerber_account_id=i_cerber_account_id;
RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


Michal Szymankis
http://blog.szymanskich.net

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.0.238 / Virus Database: 270.12.21/2103 - Release Date: 05/07/09 18:05:00


  

[GENERAL] Controlling psql output

[Gauthier, Dave]

Hi:

Using a single psql command to generate stdout in linux that will be redirected 
to a file.  Many rows with 1 column are returned.  I want no header, no footer, 
no blank lines at the top or bottom, no initial space before each record.  This 
is what I'm trying...

psql -P tuples_only=on,footer=off,border=0 mydb

This gets rid of the header and footer OK.  But there is still a blank line as 
the first line in stdout.  Also, each record has a preceding space before the 
column value.

Is there a way to do what I want?

-dave

Re: [GENERAL] Controlling psql output

[Joshua D. Drake]

On Fri, 2009-05-08 at 09:51 -0700, Gauthier, Dave wrote:
> Hi:
> 
>  
> 
> Using a single psql command to generate stdout in linux that will be
> redirected to a file.  Many rows with 1 column are returned.  I want
> no header, no footer, no blank lines at the top or bottom, no initial
> space before each record.  This is what I’m trying...
> 
>  
> 
> psql –P tuples_only=on,footer=off,border=0 mydb 

psql -A -t

j...@jd-laptop:~$ psql -A -t -U postgres -c "select * from bool_test"
f
j...@jd-laptop:~$

Sincerely,

Joshua D. Drake

> 
> 
-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Controlling psql output

[Christophe]

On May 8, 2009, at 9:51 AM, Gauthier, Dave wrote:
This gets rid of the header and footer OK.  But there is still a  
blank line as the first line in stdout.  Also, each record has a  
preceding space before the column value.


Is there a way to do what I want?


sed?

Re: [GENERAL] Controlling psql output

[Ben Chobot]

On Fri, 8 May 2009, Gauthier, Dave wrote:


Hi:

Using a single psql command to generate stdout in linux that will be redirected 
to a file.  Many rows with 1 column are returned.  I want no header, no footer, 
no blank lines at the top or bottom, no initial space before each record.  This 
is what I'm trying...

psql -P tuples_only=on,footer=off,border=0 mydb

This gets rid of the header and footer OK.  But there is still a blank line as 
the first line in stdout.  Also, each record has a preceding space before the 
column value.

Is there a way to do what I want?


Do you need to have the rows aligned? The -A flag may work for you, though 
you might want to specify a different column seperator.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Controlling psql output

[Gauthier, Dave]

-A -t worked great.  Thanks !
-dave

-Original Message-
From: Ben Chobot [mailto:be...@silentmedia.com] 
Sent: Friday, May 08, 2009 2:03 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Controlling psql output

On Fri, 8 May 2009, Gauthier, Dave wrote:

> Hi:
>
> Using a single psql command to generate stdout in linux that will be 
> redirected to a file.  Many rows with 1 column are returned.  I want no 
> header, no footer, no blank lines at the top or bottom, no initial space 
> before each record.  This is what I'm trying...
>
> psql -P tuples_only=on,footer=off,border=0 mydb
>
> This gets rid of the header and footer OK.  But there is still a blank line 
> as the first line in stdout.  Also, each record has a preceding space before 
> the column value.
>
> Is there a way to do what I want?

Do you need to have the rows aligned? The -A flag may work for you, though 
you might want to specify a different column seperator.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column oriented pgsql

[John R Pierce]

Mag Gam wrote:

Is it possible to tweak (easily) Postgresql so the storage is column
oriented versus row-oriented? We would like to increase read
optimization on our data which is about 2TB.

  


you read your tables by column, rather than by row??   

SQL queries are inherently row oriented, the fundamental unit of storage 
is a 'tuple', which is a representation of a row of a table.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote access

[John R Pierce]

George Weaver wrote:

Hi,
 
I have a client with a main office and a branch office about 90 miles 
away.
 
They have a server at the main office but it is not a web server.
 
What would be the best solution for them to access a PostgreSQL 
database located at the main office from the branch office?
 
I am not "network savvy", but if I get pointed in the right direction 
I should be able to fill in the blanks.


how are these two offices connected?   do they have any sort of 
network connection between them, or are they just both 'on the internet' 
via local firewall/routers?


if the latter, what you really want is a site to site VPN.   upgrade the 
'routers' at both ends to something that supports VPN, like a Juniper 
SSG5's (I'm assuming these are small offices), configure the two 
networks to use different subnets, like 192.168.10.xxx and 
192.168.11.xxx, and the VPN will transparently route between these.
So, if your PG server is 192.168.10.25, users on 192.168.11.xxx can just 
direcfly connect to it by IP address.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column oriented pgsql

[Christophe]

On May 8, 2009, at 11:25 AM, John R Pierce wrote:

you read your tables by column, rather than by row??
SQL queries are inherently row oriented, the fundamental unit of  
storage is a 'tuple', which is a representation of a row of a table.


I believe what is referring to is the disk storage organization,  
clustering a single column from multiple rows together onto a page.   
For example, if your typical use of a table is to read one particular  
column from a large number of rows, this could (in theory) improve  
performance.


AFAIK, PostgreSQL doesn't support this.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column oriented pgsql

[Joshua Tolley]

On Fri, May 08, 2009 at 11:25:30AM -0700, John R Pierce wrote:
> Mag Gam wrote:
>> Is it possible to tweak (easily) Postgresql so the storage is column
>> oriented versus row-oriented? We would like to increase read
>> optimization on our data which is about 2TB.
>>
>>   
>
> you read your tables by column, rather than by row??   
>
> SQL queries are inherently row oriented, the fundamental unit of storage  
> is a 'tuple', which is a representation of a row of a table.

http://en.wikipedia.org/wiki/Column_oriented_database

This has come up on the lists from time to time; the short answer is it's
really hard.

- Josh / eggyknap 


signature.asc
Description: Digital signature

[GENERAL] pg query exec time, reports

[Johnny Edge]

Hello folks,
 
I wish to log query execution time on all queries. 
 
Could you please advise re the same? 
 
Could you also suggest report generation tools? I.e. what queries take longest 
time to exec, duration of session, etc.
 
Thanks,
 
-JE

Re: [GENERAL] pg query exec time, reports

[Bill Moran]

In response to "Johnny Edge" :
>  
> I wish to log query execution time on all queries. 

http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html
Specifically the log_*_statement directives.

> Could you also suggest report generation tools? I.e. what queries take 
> longest time to exec, duration of session, etc.

pgFouine

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] prepared statements and DBD::Pg

[Daniel Verite]

Tim Bunce wrote:


The example that started this thread was that this valid statement
worked:

prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT 

INTO foo(2, 2);")


but this valid statement didn't:

prepare("			   INSERT INTO foo(1, 1); INSERT 

INTO foo(2, 2);")


My argument is that both calls should return statement handles.


I think they do, and the original report is somehow flawed. Here's a 
test that demonstrates this with the SQL pasted from the initial 
example.


 print "version is $DBD::Pg::VERSION\n";
 $dbh->{pg_server_prepare} = 1;
 my $prepare_sql =

Re: [GENERAL] prepared statements and DBD::Pg

[JP Fletcher]

Daniel Verite wrote:

Tim Bunce wrote:


The example that started this thread was that this valid statement
worked:

prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT 

INTO foo(2, 2);")


but this valid statement didn't:

prepare("   INSERT INTO foo(1, 1); INSERT 

INTO foo(2, 2);")


My argument is that both calls should return statement handles.


I think they do, and the original report is somehow flawed.
In my attempt to obfuscate the actual code, I actually included invalid 
SQL , but I can assure you that the failure occurred as I described it, 
though only with the version  2.11.8.  Other versions > 1.4 worked fine, 
despite the explanation in the DBD::Pg docs which implied that they 
might not.


Here's a test that demonstrates this with the SQL pasted from the 
initial example.


 print "version is $DBD::Pg::VERSION\n";
 $dbh->{pg_server_prepare} = 1;
 my $prepare_sql =

[GENERAL] limit-offset different result sets with same query

[Emanuel Calvo Franco]

Hi all.

I'll make this faster.

I hace this table and this function:


CREATE FUNCTION pg_round_random_range(integer, integer) RETURNS integer
LANGUAGE plperl IMMUTABLE STRICT
AS $_X$
my($imin, $imax) = @_;
if ($_[0] == $_[1]){
return $_[0];}
if($imin > $imax){
$imin = $_[1];
$imax = $_[0];}
$_number_ = ( (rand) * ($imax + 1));
while (($_number_ < $imin) && ( $_number_ > $imax)){
$_number_ = ( (rand) * ($imax + 1));}
return sprintf "%d",$_number_;

$_X$;

CREATE TABLE datos (
texto text DEFAULT md5((random())::text),
entero2 smallint DEFAULT (rpad((hashtext((random())::text))::text,
4))::smallint,
entero4 integer DEFAULT (lpad(((hashtext((random())::text))::text
|| replace((hashtext((random())::text))::text, '-'::text, ''::text)),
9))::integer,
entero8 bigint DEFAULT (rpad(((hashtext((random())::text))::text
|| replace((hashtext((random())::text))::text, '-'::text, ''::text)),
19))::bigint,
"float" double precision DEFAULT ((random() * (1000)::double
precision) + random()),
fecha date DEFAULT (now())::date,
tiempo timestamp without time zone DEFAULT now(),
ztiempo timestamp with time zone DEFAULT now(),
ip cidr DEFAULT pg_round_random_range(0, 255))::text ||
'.'::text) || (pg_round_random_range(0, 255))::text) || '.'::text) ||
(pg_round_random_range(0, 255))::text) || '.'::text) ||
(pg_round_random_range(0, 255))::text))::cidr
);

I insert several tuples to make a test with the sentence 'insert into
datos default values'.
This table don't have indexes. There are no users connected exept me
(is a local and
test database).
When i have ~16 regs i start to make some querys.

Executing 'select * from datos limit 1 offset 15' two times i have different
result sets.
When  i execute 'explain analyze verbose ' i see that (as
expected) the seq scan
is occurring.

Examples:

parapruebas=# select entero8 from datos limit 1 offset 2;
  entero8

 477808241937806077
(1 row)

parapruebas=# select entero8 from datos limit 1 offset 2;
  entero8

 477808241937806077
(1 row)
QUERY PLAN


 Limit  (cost=0.05..0.07 rows=1 width=8) (actual time=0.033..0.036
rows=1 loops=1)
   ->  Seq Scan on datos  (cost=0.00..4128.00 rows=172800 width=8)
(actual time=0.014..0.020 rows=3
loops=1)
 Total runtime: 0.107 ms
(3 rows)


In this case, on a small offset the result set returns the same. But
in higher offsets:

parapruebas=# select entero8 from datos limit 1 offset 10;
  entero8

 -82136193203177195
(1 row)

parapruebas=# select entero8 from datos limit 1 offset 10;
   entero8
-
 1201794554456297856
(1 row)
 QUERY PLAN


 Limit  (cost=2388.89..2388.91 rows=1 width=8) (actual
time=622.198..622.201 rows=1 loops=1)
   ->  Seq Scan on datos  (cost=0.00..4128.00 rows=172800 width=8)
(actual time=0.014..356.800 rows=
11 loops=1)
 Total runtime: 622.247 ms
(3 rows)



That's correct? Is logical that if the scan is sequential in the
physical table returns differents
data?

I test it on 8.4 beta1 and 8.3.5 and 8.3.7 with the same results.

Thanks in advance.

-- 
  Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] limit-offset different result sets with same query

[Tom Lane]

Emanuel Calvo Franco  writes:
> Executing 'select * from datos limit 1 offset 15' two times i have 
> different
> result sets.

The "synchronous scan" logic is probably responsible.  Turn off
synchronize_seqscans if this behavior bothers you.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] limit-offset different result sets with same query

[Emanuel Calvo Franco]

2009/5/8 Tom Lane :
> Emanuel Calvo Franco  writes:
>> Executing 'select * from datos limit 1 offset 15' two times i have 
>> different
>> result sets.
>
> The "synchronous scan" logic is probably responsible.  Turn off
> synchronize_seqscans if this behavior bothers you.
>
>                        regards, tom lane
>

It works Tom,
Thanks!

-- 
  Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] limit-offset different result sets with same query

[David Fetter]

On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote:
> Hi all.
> 
> I'll make this faster.
> 
> I hace this table and this function:

You should only ever assume that your SELECT's output will have a
particular ordering when you include an ORDER BY clause that actually
specifies the order well enough :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] limit-offset different result sets with same query

[Emanuel Calvo Franco]

2009/5/8 David Fetter :
> On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote:
>> Hi all.
>>
>> I'll make this faster.
>>
>> I hace this table and this function:
>
> You should only ever assume that your SELECT's output will have a
> particular ordering when you include an ORDER BY clause that actually
> specifies the order well enough :)
>

I test it in the first time :)

With the 'order by' it works well, but in 'theory' if you
run sequentially and physically a table, you expect obtain the same
results with a same query.
There is no indexes that can intersect the results or inherits
tables.

But with the option synchronize_seqscans in off, it works like
i expected :)

i will study a bit more this option on monday (like always).


-- 
  Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] limit-offset different result sets with same query

[Alvaro Herrera]

David Fetter escribió:
> On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote:
> > Hi all.
> > 
> > I'll make this faster.
> > 
> > I hace this table and this function:
> 
> You should only ever assume that your SELECT's output will have a
> particular ordering when you include an ORDER BY clause that actually
> specifies the order well enough :)

Yeah, we went over this on the spanish list, turned out that I couldn't
remember about syncscan :-)

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] limit-offset different result sets with same query

[Adam Rich]

Emanuel Calvo Franco wrote:
>
> Executing 'select * from datos limit 1 offset 15' two times i 
have different

> result sets.
> When  i execute 'explain analyze verbose ' i see that (as
> expected) the seq scan
> is occurring.
>
>
> That's correct? Is logical that if the scan is sequential in the
> physical table returns differents
> data?
>
> I test it on 8.4 beta1 and 8.3.5 and 8.3.7 with the same results.
>
> Thanks in advance.
>


Emanuel,
LIMIT and OFFSET are stable only when you have "ORDER BY" on unique
values.  Without that, the database is free to return the rows in
whatever order it deems best, which gives unpredictable results when
combined with LIMIT/OFFSET.

Adam


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column oriented pgsql

[Mag Gam]

Got it thanks!



On Fri, May 8, 2009 at 2:57 PM, Christophe  wrote:
>
> On May 8, 2009, at 11:25 AM, John R Pierce wrote:
>>
>> you read your tables by column, rather than by row??
>> SQL queries are inherently row oriented, the fundamental unit of storage
>> is a 'tuple', which is a representation of a row of a table.
>
> I believe what is referring to is the disk storage organization, clustering
> a single column from multiple rows together onto a page.  For example, if
> your typical use of a table is to read one particular column from a large
> number of rows, this could (in theory) improve performance.
>
> AFAIK, PostgreSQL doesn't support this.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column oriented pgsql

[John R Pierce]

Joshua Tolley wrote:

http://en.wikipedia.org/wiki/Column_oriented_database
This has come up on the lists from time to time; the short answer is it's
really hard.
  


indeed.  among other issues is, just what order should those columns be 
stored in?  database tables have no implicit order, they are abstractly 
unordered sets of rows.  an index can impose an order but a given table 
can have multiple indexes, and a given query can sort on most any 
arbitrary thing it wants.   and, say you are storing the columns sorted 
by the primary key,  how do you do inserts or updates that change this 
order?


instead of one table with (key, v1, v2, v3) how about N tables, (k,v1), 
(k,v2), (k,v3)  ?   or at least, one extra table with just the value 
that you want columnar access to?








--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column oriented pgsql

[bfriedman.postgresql]

If you are looking for a column based dbms, you might want to check out 
Monet - it is a columnar database.


http://monetdb.cwi.nl/

For some applications, columnar databases can be much faster than 
traditional rdbms systems.  However, column based databases are not a 
'one size fits all' answer. 


Brent Friedman

Mag Gam wrote:

Got it thanks!



On Fri, May 8, 2009 at 2:57 PM, Christophe  wrote:
  

On May 8, 2009, at 11:25 AM, John R Pierce wrote:


you read your tables by column, rather than by row??
SQL queries are inherently row oriented, the fundamental unit of storage
is a 'tuple', which is a representation of a row of a table.
  

I believe what is referring to is the disk storage organization, clustering
a single column from multiple rows together onto a page.  For example, if
your typical use of a table is to read one particular column from a large
number of rows, this could (in theory) improve performance.

AFAIK, PostgreSQL doesn't support this.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




  

Re: [GENERAL] limit-offset different result sets with same query

[David Fetter]

On Fri, May 08, 2009 at 06:40:33PM -0300, Emanuel Calvo Franco wrote:
> 2009/5/8 David Fetter :
> > On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote:
> >> Hi all.
> >>
> >> I'll make this faster.
> >>
> >> I hace this table and this function:
> >
> > You should only ever assume that your SELECT's output will have a
> > particular ordering when you include an ORDER BY clause that
> > actually specifies the order well enough :)
> >
> 
> I test it in the first time :)
> 
> With the 'order by' it works well, but in 'theory'

The theory under which you should operate is that the underlying
implementation only gives you the orderings you ask for.  This way,
when other beneficial implementation changes happen, they will not
surprise you. :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general