Thread: pgsql vs mysql

Hi,

I am new to pgsql....I really appreciate the licensing terms of
pgsql...mysql licensing is a little bit risky to use...

But, I have one question...

Q. when using pgsql as a very heavy and mission critical applications,
what are the advantages of using pgsql instead of mysql?



thanks.

On 30 Jun 2006 08:58:27 -0700, howachen@gmail.com <howachen@gmail.com> wrote:
> I am new to pgsql....I really appreciate the licensing terms of
> pgsql...mysql licensing is a little bit risky to use...
>
> Q. when using pgsql as a very heavy and mission critical applications,
> what are the advantages of using pgsql instead of mysql?

ok, this is pretty much a faq and you can get lots of information
about this from the archives and from the internet itself.  However,
there is tons of disinformation about there so be careful  Here are
the major points you need to consider, based on my experience:

* mysql performance advantage is greatly overstated, although
postgresql requires you to use certain conventions (example: prepared
statements) to get comparable performance
* both databases (IMO) are very stable. in 6 years of workikng with
both databases, I've never had either 'just crash' without external
mitigating circumstances, a testimonial to both projects
* mysql tends to encourage development in application code, while pg
tends to encourage development in the database iteself.  For various
reasons, I greatly prefer the latter.
* pg, in my opinion, has a better unicode handling, although there is
a small learning curve to do it the best way
* pg mvcc transactional engine is better than innodb (IMO), and faster
when used properly
* pg pl/pgsql is much better than (mysql 5.0) stored procedures. you
also have a lot of other languages to use if you want
* pg is generally much more flexible and extensible
* mysql has decent out of the box replication that is easy to set up
(one day I hope pg get hot PITR which is analagous feature)
* pg has IMO much better shell and standardized syntax
* pg query planer rivals top commercial databse engines
* mysql has a few features here and there which are nice...just to
name a few, flush tables with lock, multiple insert, etc

there are lots of other things.  if you want to use a mission critcal
database engine for new project, I believe there to be only one
choice, but you have to take a lot of things into consideration;
support, development style, and a host of other factors.  If you
follow the pg lists I think you will find the support here to be
unbelievably good.

Merlin

Merlin Moncure wrote:
>
> * mysql performance advantage is greatly overstated, although
> postgresql requires you to use certain conventions (example: prepared
> statements) to get comparable performance
> * both databases (IMO) are very stable. in 6 years of workikng with
> both databases, I've never had either 'just crash' without external
> mitigating circumstances, a testimonial to both projects
> * mysql tends to encourage development in application code, while pg
> tends to encourage development in the database iteself.  For various
> reasons, I greatly prefer the latter.
> * pg, in my opinion, has a better unicode handling, although there is
> a small learning curve to do it the best way
> * pg mvcc transactional engine is better than innodb (IMO), and faster
> when used properly
> * pg pl/pgsql is much better than (mysql 5.0) stored procedures. you
> also have a lot of other languages to use if you want
> * pg is generally much more flexible and extensible
> * mysql has decent out of the box replication that is easy to set up
> (one day I hope pg get hot PITR which is analagous feature)
> * pg has IMO much better shell and standardized syntax
> * pg query planer rivals top commercial databse engines
> * mysql has a few features here and there which are nice...just to
> name a few, flush tables with lock, multiple insert, etc
>

This is a very good list of differences.  I'm going to elaborate
slightly on this one: "pg is generally much more flexible and extensible."

Mysql in general implements features as an afterthought.  This causes it
to be a bit less flexible than postgres.  While it does contain what 99%
of people want to use (probably because they only use mysql), it has
huge gaps in it's feature set.  Here is a great example of what I'm
talking about:

Mysql does not allow you to use now() as the default value of a column.
 From their docs:

"The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a function
or an expression. This means, for example, that you cannot set the
default for a date column to be the value of a function such as NOW() or
CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as
the default for a TIMESTAMP column. See Section 11.3.1.1, “TIMESTAMP
Properties as of MySQL 4.1”. "

So they work around this major shortcoming by giving people the
CURRENT_TIMESTAMP constant for the timestamp column so at least the
people wanting an automatic timestamps are happy.  Basically they add
just enough support to do the most common thing.

Postgres, allows all of the functions in a create table statement.  This
is legit in postgres:

create table test_tab (data varchar(20), timestamp timestamp default
now() - interval '32.56 minutes');

HTH,

schu

> Mysql does not allow you to use now() as the default value of a column.
>  From their docs:
>
> "The DEFAULT clause specifies a default value for a column. With one
> exception, the default value must be a constant; it cannot be a function
> or an expression. This means, for example, that you cannot set the
> default for a date column to be the value of a function such as NOW() or
> CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as
> the default for a TIMESTAMP column. See Section 11.3.1.1, "TIMESTAMP
> Properties as of MySQL 4.1". "
>
> So they work around this major shortcoming by giving people the
> CURRENT_TIMESTAMP constant for the timestamp column so at least the
> people wanting an automatic timestamps are happy.  Basically they add
> just enough support to do the most common thing.
>
> Postgres, allows all of the functions in a create table statement.  This
> is legit in postgres:
>
> create table test_tab (data varchar(20), timestamp timestamp default
> now() - interval '32.56 minutes');

right mysql implementation of defaults is a complete disaster, a big
contributing factor as to why mysql tends to favor application code.
In contrast, pg follows the principle of least suprise, becuase the
internal structures are open and highly generic. the syntax is
standardized and regular (as much as with possible somehow) wherever
possible with carefully thought out exceptions.

mysql syntax, otoh,  is hacky and full of special cases.  each basic
sql command is highly nuanced and full of legacy workarounds to
limitations of previous versions of mysql. some of the specific
commands (insert, especially) are quite powerful but the whole adds up
to less than the sum of its parts somehow.

On Fri, Jun 30, 2006 at 01:07:32PM -0400, Merlin Moncure wrote:
> * mysql has decent out of the box replication that is easy to set up
> (one day I hope pg get hot PITR which is analagous feature)

Actually, PITR is in no way analagous. Try replicating something like
'INSERT INTO table SELECT random();' on MySQL and note how all the data
is different.

pgpool replication is equivalent to MySQL's replication. Or if you want
more sophisticated replication, use Slony.

BTW, anyone curious about the differences should take a look at
google:'mysql gotchas' (there's also a PostgreSQL section on that site).
See also the MySQL/PostgreSQL thread that was on this list yesterday.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

"Merlin Moncure" 寫道：

> On 30 Jun 2006 08:58:27 -0700, howachen@gmail.com <howachen@gmail.com> wrote:
> > I am new to pgsql....I really appreciate the licensing terms of
> > pgsql...mysql licensing is a little bit risky to use...
> >
> > Q. when using pgsql as a very heavy and mission critical applications,
> > what are the advantages of using pgsql instead of mysql?
>
> ok, this is pretty much a faq and you can get lots of information
> about this from the archives and from the internet itself.  However,
> there is tons of disinformation about there so be careful  Here are
> the major points you need to consider, based on my experience:
>
> * mysql performance advantage is greatly overstated, although
> postgresql requires you to use certain conventions (example: prepared
> statements) to get comparable performance
> * both databases (IMO) are very stable. in 6 years of workikng with
> both databases, I've never had either 'just crash' without external
> mitigating circumstances, a testimonial to both projects
> * mysql tends to encourage development in application code, while pg
> tends to encourage development in the database iteself.  For various
> reasons, I greatly prefer the latter.
> * pg, in my opinion, has a better unicode handling, although there is
> a small learning curve to do it the best way
> * pg mvcc transactional engine is better than innodb (IMO), and faster
> when used properly
> * pg pl/pgsql is much better than (mysql 5.0) stored procedures. you
> also have a lot of other languages to use if you want
> * pg is generally much more flexible and extensible
> * mysql has decent out of the box replication that is easy to set up
> (one day I hope pg get hot PITR which is analagous feature)
> * pg has IMO much better shell and standardized syntax
> * pg query planer rivals top commercial databse engines
> * mysql has a few features here and there which are nice...just to
> name a few, flush tables with lock, multiple insert, etc
>
> there are lots of other things.  if you want to use a mission critcal
> database engine for new project, I believe there to be only one
> choice, but you have to take a lot of things into consideration;
> support, development style, and a host of other factors.  If you
> follow the pg lists I think you will find the support here to be
> unbelievably good.
>
> Merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

thanks. very useful!

On 6/30/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> Actually, PITR is in no way analagous. Try replicating something like
> 'INSERT INTO table SELECT random();' on MySQL and note how all the data
> is different.
>
> pgpool replication is equivalent to MySQL's replication. Or if you want
> more sophisticated replication, use Slony.

i think you are missing the point here...mysql binary log replication
is asynchronous and log based. it has some limitations and gotchas
like you noted but feels a lot like the pg pitr mechanism.  you get a
lot of power and use for a relatively little administative overhead
and an easy set up.  you can almost, but not quite, do the same thing
with pg pitr, although pitr is useful for a lot of other things.

slony, of course, is quite sophisticated and powerful but fills a
different need, i think.

> BTW, anyone curious about the differences should take a look at
> google:'mysql gotchas' (there's also a PostgreSQL section on that site).
> See also the MySQL/PostgreSQL thread that was on this list yesterday.

hm. that's all very true (and important), but I try and keep focus on
the things besides basic correctness that drive the development
cultural divide that seperates the two communities.  pg, besides being
a very project and surrounded by wonderful people, 'feels' right when
solving problems.  why does it feel right?  what kinds of things in
the database influence the development culture?  pg satisfies me on a
much deeper level that transcends feature 'x' or 'y' but stems from
something much more vital and vibrant.  it seems like the biggest
brains who really 'get it' are here, and that's why I'm here.

merlin

In an attempt to throw the authorities off his trail, mmoncure@gmail.com ("Merlin Moncure") transmitted:
> hm. that's all very true (and important), but I try and keep focus
> on the things besides basic correctness that drive the development
> cultural divide that seperates the two communities.  pg, besides
> being a very project and surrounded by wonderful people, 'feels'
> right when solving problems.  why does it feel right?  what kinds of
> things in the database influence the development culture?  pg
> satisfies me on a much deeper level that transcends feature 'x' or
> 'y' but stems from something much more vital and vibrant.  it seems
> like the biggest brains who really 'get it' are here, and that's why
> I'm here.

One cultural divide is that MySQL development takes place inside one
private company, whereas PostgreSQL is developed by what truly is a
global community.  That difference has a lot of side-effects.

One thing that doesn't quite stem directly from that is that there are
definitely some deep thinkers in the PostgreSQL community.  And they
consistently think beyond the scope of any given immediate problem.
They don't just try to patch over some immediate issue; they try to
see if there's a further issue.

For instance, we recently (in the last year) had some challenges
compiling PostgreSQL on AIX, and reported as much.  An "immediate"
resolution might have been to tweak the config script a little.  What
we got instead was something of an audit of "weird libs still in use."
A number of build changes were made to assortedly remove now-obsolete
libs, and to have specific programs compile in only those libraries
that they actually need.  We'll see, when 8.2 is getting deployed, if
this fully addresses our AIX issues; I expect it's close.

What was interesting was that not only was the fix completely
different from what was expected, but it became a more ambitious
change that cleans up things on a lot of platforms.

That seems not uncommon; someone comes, thinking they have The Answer
to something.  Further examination shows that it wasn't the right
question, but someone figures out what that question should have been
:-).
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxdatabases.info/info/internet.html
Een schip op het strand is een baken in zee.

Merlin Moncure wrote:

> * mysql has a few features here and there which are nice...just to
> name a few, flush tables with lock, multiple insert, etc

PostgreSQL does have multiple inserts, but complying to the SQL92
standard, through INSERT (...) SELECT ...
For example:
INSERT INTO my_table (id, name)
SELECT 1, 'John'
UNION ALL SELECT 2, 'Hank'
UNION ALL SELECT 3, 'Charles";

Just to mention that is _is_ possible, just not very pretty.

Next to that, there's the COPY statement of course, with which you can
insert a whole bunch of (properly escaped) data from STDIN.

Now that I think of it... Is there an easy way to escape data for use
with COPY? I know the PQEscapeConnection functions and the like, but
that's a different escaping... I suppose through pg_dump some
functionality might be available to do so...?

It'd be ideal for my case if it'd be possible to do the escaping from
within an XSLT sheet, but that seems unlikely. Can't hurt to ask though ;)

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

On Sat, 2006-07-01 at 22:27, Christopher Browne wrote:
> In an attempt to throw the authorities off his trail, mmoncure@gmail.com ("Merlin Moncure") transmitted:
> > hm. that's all very true (and important), but I try and keep focus
> > on the things besides basic correctness that drive the development
> > cultural divide that seperates the two communities.  pg, besides
> > being a very project and surrounded by wonderful people, 'feels'
> > right when solving problems.  why does it feel right?  what kinds of
> > things in the database influence the development culture?  pg
> > satisfies me on a much deeper level that transcends feature 'x' or
> > 'y' but stems from something much more vital and vibrant.  it seems
> > like the biggest brains who really 'get it' are here, and that's why
> > I'm here.
>
> One cultural divide is that MySQL development takes place inside one
> private company, whereas PostgreSQL is developed by what truly is a
> global community.  That difference has a lot of side-effects.
>
> One thing that doesn't quite stem directly from that is that there are
> definitely some deep thinkers in the PostgreSQL community.  And they
> consistently think beyond the scope of any given immediate problem.
> They don't just try to patch over some immediate issue; they try to
> see if there's a further issue.
>
> For instance, we recently (in the last year) had some challenges
> compiling PostgreSQL on AIX, and reported as much.  An "immediate"
> resolution might have been to tweak the config script a little.  What
> we got instead was something of an audit of "weird libs still in use."
> A number of build changes were made to assortedly remove now-obsolete
> libs, and to have specific programs compile in only those libraries
> that they actually need.  We'll see, when 8.2 is getting deployed, if
> this fully addresses our AIX issues; I expect it's close.
>
> What was interesting was that not only was the fix completely
> different from what was expected, but it became a more ambitious
> change that cleans up things on a lot of platforms.
>
> That seems not uncommon; someone comes, thinking they have The Answer
> to something.  Further examination shows that it wasn't the right
> question, but someone figures out what that question should have been
> :-).

Now, contrast and compare that to these two mysql bugs in their RPM
packages:

http://bugs.mysql.com/bug.php?id=15223
http://bugs.mysql.com/bug.php?id=15255

Basically, a problem on bsd with libz resulted in mysql taking the road
of "let's just include out own libz".

Of course, the implementation broke building php from source, which is
pretty common when you need odd things like oracle or cracklib or ldap
or whatever.  This problem showed up at then end of November 2005, and
has pretty much only had a bandaid placed over it.

The response I got from MySQL, since this problem affected us (we hit
MySQL, PostgreSQL and Oracle from our PHP enabled app server) was that
we should pay for commercial support.  when I asked how that would help,
I was told that commercial support runs on older, more stable versions
of MySQL.  That's not the kind of thing that makes me want to buy
commercial support.  I can download and run older versions of MySQL all
by myself.

On 6/30/2006 1:07 PM, Merlin Moncure wrote:

> * mysql has a few features here and there which are nice...just to
> name a few, flush tables with lock, multiple insert, etc

I have no clue what flushing tables with lock might be good for. Are
applications in MySQuirreL land usually smarter than the DB engine with
respect to when to checkpoint or not?

The multiple insert stuff is not only non-standard, it also encourages
the bad practice of using literal values directly in the SQL string
versus prepared statements with place holders. It is bad practice
because it introduces SQL injection risks since the responsibility of
literal value escaping is with the application instead of the driver.

Everything that teaches new developers bad things counts as a
disadvantage in my book, so -1 on that for MySQL too.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On 7/11/06, Jan Wieck <JanWieck@yahoo.com> wrote:
> On 6/30/2006 1:07 PM, Merlin Moncure wrote:
> I have no clue what flushing tables with lock might be good for. Are
> applications in MySQuirreL land usually smarter than the DB engine with
> respect to when to checkpoint or not?

no, but the ability to flip a database into read only mode with such
an easy command is relatively useful. i very much pg had a read only
mode, btw.

> The multiple insert stuff is not only non-standard, it also encourages
> the bad practice of using literal values directly in the SQL string
> versus prepared statements with place holders. It is bad practice
> because it introduces SQL injection risks since the responsibility of
> literal value escaping is with the application instead of the driver.

good points, and pg can accomplish similar via insert select union
all, etc., but mysql version of same is better syntax imo, so i guess
i should take it up with the sql standard.  As to preparing
statements, I agree in principle although I don't know if that is a
good argument not to make the non-paramaterized interface more
powerful.

> Everything that teaches new developers bad things counts as a
> disadvantage in my book, so -1 on that for MySQL too.

no comment. :)  small disclaimer:  I am right now administrating a
relatively large mysql database infrastructure which I inherited.  I
was hired with for the express purpose of converting it to pg.
meanwhile since writing the op I got burned really badly by the mysql
replication where the slave became off synch with master on an
important table, something you might appreciate.

that said, i tried to put fairness in my comparison, many pg/mysql
comparisons ulimately resort to a dismissive mysql diss which does not
play well to the uninformed third party.  so, I made an attempt at
something with some substance.

regards,
merlin

On Tue, 2006-07-11 at 11:04, Jan Wieck wrote:
> On 6/30/2006 1:07 PM, Merlin Moncure wrote:
>
> > * mysql has a few features here and there which are nice...just to
> > name a few, flush tables with lock, multiple insert, etc

> The multiple insert stuff is not only non-standard, it also encourages
> the bad practice of using literal values directly in the SQL string
> versus prepared statements with place holders.

I thought it was in the SQL 99 standard...

>  It is bad practice
> because it introduces SQL injection risks since the responsibility of
> literal value escaping is with the application instead of the driver.

agreed, however.

On 7/11/2006 1:08 PM, Scott Marlowe wrote:

> On Tue, 2006-07-11 at 11:04, Jan Wieck wrote:
>> On 6/30/2006 1:07 PM, Merlin Moncure wrote:
>>
>> > * mysql has a few features here and there which are nice...just to
>> > name a few, flush tables with lock, multiple insert, etc
>
>> The multiple insert stuff is not only non-standard, it also encourages
>> the bad practice of using literal values directly in the SQL string
>> versus prepared statements with place holders.
>
> I thought it was in the SQL 99 standard...

The SQL bible doesn't say SQL99, it says it is a DB2 specific feature.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:

> As to preparing
> statements, I agree in principle although I don't know if that is a
> good argument not to make the non-paramaterized interface more
> powerful.

It is not, as prepared statements have the problem that they are only
optimized once and very generically and without actual knowledge of
the parameter content, this is just useless.

I had the problem a few months ago, where my app server plugin and
the jdbc driver used prepared statements for selecting stuff from the
database. Most of the time, indexes weren't used at all, so
PostgreSQL performance was the worst I've ever seen in this environment.

There are fixes for that, but it should be made easier ...

cug

On 7/11/2006 1:36 PM, Merlin Moncure wrote:

> that said, i tried to put fairness in my comparison, many pg/mysql
> comparisons ulimately resort to a dismissive mysql diss which does not
> play well to the uninformed third party.  so, I made an attempt at
> something with some substance.

Totally understood. The life vest that is not worn because it is too
complicated to put on doesn't save any lives. Meaning the simplicity of
setting up MySQL replication means it is used more often. One just has
to keep in mind to rebuild the slaves from time to time because they get
out of sync without any visible failure. Slony-I on the other hand is a
steeper learning curve, and although it could serve as a much more
reliable backup solution, it isn't used nearly as often as it should.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

> The multiple insert stuff is not only non-standard, it also encourages
> the bad practice of using literal values directly in the SQL string
> versus prepared statements with place holders. It is bad practice
> because it introduces SQL injection risks since the responsibility of
> literal value escaping is with the application instead of the driver.

It is also something that users are clammoring for (and my customers). To
the point that I have customers using unions to emulate the behavior. Why?
Because it is really, really fast.

Joshua D. Drake


--
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

On 7/11/06, Guido Neitzer <guido.neitzer@pharmaline.de> wrote:
> On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:
>
> > As to preparing
> > statements, I agree in principle although I don't know if that is a
> > good argument not to make the non-paramaterized interface more
> > powerful.
>
> It is not, as prepared statements have the problem that they are only
> optimized once and very generically and without actual knowledge of
> the parameter content, this is just useless.
>

there is some confusion (not necessarily by you) between paramaterized
and prepared statements.  parameterized is when the query parameters
are separate from the query string itself, you can do this with or
without preparing them.  parameterizing statements is  basically
always a good thing...you get something for nothing.

preparing can work great or not depending on what you are trying to
do.  If they work then can cut as much of 50% of the query time and if
they dont work...well you know what happens.  I can vouch for this,
for example I like to parameterize the limit clause but this can
confuse the planner.  Still, overall, when used carefully and
properly, they can supercharge your server.

merlin

Guido Neitzer wrote:
> On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:
>
> >As to preparing
> >statements, I agree in principle although I don't know if that is a
> >good argument not to make the non-paramaterized interface more
> >powerful.
>
> It is not, as prepared statements have the problem that they are only
> optimized once and very generically and without actual knowledge of
> the parameter content, this is just useless.
>
> I had the problem a few months ago, where my app server plugin and
> the jdbc driver used prepared statements for selecting stuff from the
> database. Most of the time, indexes weren't used at all, so
> PostgreSQL performance was the worst I've ever seen in this environment.

I'm pretty excited about this idea of yours on how to fix this problem.
Does it involve the histogram at all?

Maybe we could check the MCVs, and store those for which the plan would
be A (say indexscan) and those for which it would be B (say bitmap
indexscan), etc; so we'd save more than one plan and choose at execution
time depending on the actual parameters.  For all values not on the
MCV set, use the same plan as the least common of the MCVs.

But of course, I know nothing about the optimizer so I'm not sure if
this makes any sense at all.

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

Jan Wieck <JanWieck@Yahoo.com> writes:
> On 7/11/2006 1:08 PM, Scott Marlowe wrote:
>> I thought it was in the SQL 99 standard...

> The SQL bible doesn't say SQL99, it says it is a DB2 specific feature.

If you're speaking of INSERT INTO foo VALUES (a, row), (another, row), ...
that's in SQL92.  See 7.2 <table value constructor>:

         <table value constructor> ::=
              VALUES <table value constructor list>

         <table value constructor list> ::=
              <row value constructor> [ { <comma> <row value constructor> }... ]

It's really pretty lame that we still don't have any support at all for
this :-(.  Allowing it everywhere the spec says <table value constructor>
should be allowed might be nontrivial ... but maybe we should just fix
the INSERT ... VALUES case for now.

            regards, tom lane

Jan Wieck wrote:
> On 7/11/2006 1:08 PM, Scott Marlowe wrote:
>
>> On Tue, 2006-07-11 at 11:04, Jan Wieck wrote:
>>> On 6/30/2006 1:07 PM, Merlin Moncure wrote:
>>>
>>> > * mysql has a few features here and there which are nice...just to
>>> > name a few, flush tables with lock, multiple insert, etc
>>
>>> The multiple insert stuff is not only non-standard, it also
>>> encourages the bad practice of using literal values directly in the
>>> SQL string versus prepared statements with place holders.
>>
>> I thought it was in the SQL 99 standard...
>
> The SQL bible doesn't say SQL99, it says it is a DB2 specific feature.

hmm:

http://troels.arvin.dk/db/rdbms/#insert

says otherwise - or are we talking a different "multiple insert" ?


Stefan

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Joshua D. Drake wrote:
>> The multiple insert stuff is not only non-standard, it also encourages
>> the bad practice of using literal values directly in the SQL string
>> versus prepared statements with place holders. It is bad practice
>> because it introduces SQL injection risks since the responsibility of
>> literal value escaping is with the application instead of the driver.
>
> It is also something that users are clammoring for (and my customers). To
> the point that I have customers using unions to emulate the behavior. Why?
> Because it is really, really fast.

When inserting multiple rows in the same INSERT statement, how do
you tell which row fails on a constraint or datatype-mismatch violation?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEtAkFS9HxQb37XmcRAvfaAJ4viPqLt8g1aHR6H9l7lEjM13EWcgCgulAx
rPuCH7OSDeb7RuKBuywm5k4=
=RRQx
-----END PGP SIGNATURE-----

On 11.07.2006, at 21:11 Uhr, Alvaro Herrera wrote:

>> I had the problem a few months ago, where my app server plugin and
>> the jdbc driver used prepared statements for selecting stuff from the
>> database. Most of the time, indexes weren't used at all, so
>> PostgreSQL performance was the worst I've ever seen in this
>> environment.
>
> I'm pretty excited about this idea of yours on how to fix this
> problem.
> Does it involve the histogram at all?

There is no idea. It's only not using prepared statements right now.
You can force this in the jdbc driver (using protocolVersion=2 in the
connection url) or you can send different stuff from the application.
There is nothing where the DB itself does anything.

Nothing special.

cug

Hi All,

I have a database where I give priviledges solely by user membership in
permitted roles (groups).

It works flowlessly, but when I tried to assign CREATEUSER priviledge to
an administrator ROLE (just one database administrator, not the
postmaster), I have to explicitly "SET ROLE ADMIN" before an attempt to
"CREATE USER ...". I don't have to do that to access tables/ views/
sequences, the group priviledges work OK just from being a member of
relevant group without the necesity to set current_role explicitly.

Is this a feature or a bug? Why?

My PostgresQL is v8.1.4.

And on a similar token: "SELECT current_user" works, while "SELECT
current_database" doesn't; yet "SELECT current_user()" doesn't work,
while "SELECT current_database()" does. Is this a feature or a bug?
--
-R

On Tue, 2006-07-11 at 15:24 -0500, Ron Johnson wrote:
> Joshua D. Drake wrote:
> > It is also something that users are clammoring for (and my customers). To
> > the point that I have customers using unions to emulate the behavior. Why?
> > Because it is really, really fast.
>
> When inserting multiple rows in the same INSERT statement, how do
> you tell which row fails on a constraint or datatype-mismatch violation?

BTW. COPY from psql client is quite trivial (we learn that from
pg_dump), but can someone hint on how to use it from perl scrypt? With
"use DBI" module?


--
-R

On Tue, 2006-07-11 at 21:11, Alvaro Herrera wrote:
> Maybe we could check the MCVs, and store those for which the plan would
> be A (say indexscan) and those for which it would be B (say bitmap
> indexscan), etc; so we'd save more than one plan and choose at execution
> time depending on the actual parameters.  For all values not on the
> MCV set, use the same plan as the least common of the MCVs.

I think Tom had some plans to more aggressively cache query plans. I
wonder if this concept could be extended to cache some kind of decision
trees for query templates, i.e. make a decision tree for a specific SQL
fragment containing different plans based on the values of the contained
parameters. This could be made to match sqls with constant values too,
by considering the constant values to be potential parameters. Then a
next call with a different (constant or parameterized) value would still
match the cached decision tree...

The decisions could be done based on value ranges of the parameters, or
value lists, or whatever the planner finds to make a plan difference
based on the statistics it has when planning.

The whole thing is like discovering the main plan possibilities once,
save it, and then choose one on runtime based on the actual value of the
parameters which have been decided to matter on planning time. This
would reduce a lot the planning time (you have to do it once for a type
of query), and make the actually chosen plan dependent on the parameters
too with little overhead.

>
> But of course, I know nothing about the optimizer so I'm not sure if
> this makes any sense at all.

Well, this applies to me too, with the same conclusion :-)

Cheers,
Csaba.

On 7/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Guido Neitzer wrote:
> > On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:
> > It is not, as prepared statements have the problem that they are only
> > optimized once and very generically and without actual knowledge of
> > the parameter content, this is just useless.
> >
> > I had the problem a few months ago, where my app server plugin and
> > the jdbc driver used prepared statements for selecting stuff from the
> > database. Most of the time, indexes weren't used at all, so
> > PostgreSQL performance was the worst I've ever seen in this environment.
>
> I'm pretty excited about this idea of yours on how to fix this problem.
> Does it involve the histogram at all?

I've thought for a long time the best way to handle this problem is to
allow supplying a hint for any parameter.  For example, I like to
paramterize the limit statement whicih is actually pretty neat but the
planner (iirc) assumes 10% and so resorts to seqscan/bitmapscan in
these cases.

Merlin

On 7/12/06 3:32 AM, "Rafal Pietrak" <rafal@zorro.isa-geek.com> wrote:

> On Tue, 2006-07-11 at 15:24 -0500, Ron Johnson wrote:
>> Joshua D. Drake wrote:
>>> It is also something that users are clammoring for (and my customers). To
>>> the point that I have customers using unions to emulate the behavior. Why?
>>> Because it is really, really fast.
>>
>> When inserting multiple rows in the same INSERT statement, how do
>> you tell which row fails on a constraint or datatype-mismatch violation?
>
> BTW. COPY from psql client is quite trivial (we learn that from
> pg_dump), but can someone hint on how to use it from perl scrypt? With
> "use DBI" module?
>

See the DBD::Pg documentation, under "copy support".

Sean

# JanWieck@Yahoo.com / 2006-07-11 12:04:07 -0400:
> On 6/30/2006 1:07 PM, Merlin Moncure wrote:
>
> >* mysql has a few features here and there which are nice...just to
> >name a few, flush tables with lock, multiple insert, etc

    (...)

> The multiple insert stuff is not only non-standard, it also encourages
> the bad practice of using literal values directly in the SQL string
> versus prepared statements with place holders. It is bad practice
> because it introduces SQL injection risks since the responsibility of
> literal value escaping is with the application instead of the driver.
>
> Everything that teaches new developers bad things counts as a
> disadvantage in my book, so -1 on that for MySQL too.

    Those "multiple inserts" are really inserts with Table Value
    Constructors, which are "table literals", SQL:2003 F641 (see
    http://www.postgresql.org/docs/8.1/static/unsupported-features-sql-standard.html).
    TVCs are useful in many more contexts than just inserts. An email from a past
    thread on this list:

    : # mag@fbab.net / 2005-09-20 20:45:21 +0200:
    : > I was thinking if this was possible in some way..
    : > I have this table where we have X and Y coordinates, and i need to
    : > select several in one go.
    : >
    : > # select * from xy where (x = 1 and y = 2) or (x = 2 and y = 2);
    : >
    : > This works but are not so nice looking.
    : > It would be nice to be able to do it like this:
    : >
    : > # select * from xy where (x, y) in ((1, 2), (2, 2));
    : >
    : > But that doesn't work.
    : > A funny thing is that this works:
    : >
    : > # select * from xy where (x, y) = (1, 2);
    : >
    : > What's the most efficient way of doing these kind of selects?
    :
    :     You'd need to write that as
    :
    :     (x, y) IN VALUES (1, 2), (2, 2)
    :
    :     Unfortunately, PostgreSQL's support for table value constructors
    :     is very weak.


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