Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. PHP developers don't have time to invest in learning deep SQL. This is true, and it is a big problem IMHO. It results in lots of slow, broken, insecure database designs. ALL the web apps that I've done CPR ressuscitation on follow the same schema : - devs are database noobs - generous use of MyISAM - numerous queries, most of them unoptimized and/or useless - use of Apache/mod_php instead of fastcgi - sometimes, use of a huge slow bloated CMS/framework which issues even more unoptimized and/or useless SQL queries - site gains popularity - huge traffic takes an unprepared team by surprise (never heard of stuff like concurrency or scaling) - site fails horribly That said, I've got a 150.000+ members forum running on MySQL with sub 5 ms page times on a low-end server, it works if you do it right. Most opensource PHP apps developers have to expend lots of efforts to work on MyISAM that doesn't support foreign keys or constraints. If those resources could be directed to useful work instead of wasted like this, the result would be a lot better. The irony is that even with all that effort, you can't make a web app work without transactions, sooner or later your database integrity will fail. My theory on this is simple : - PHP is a very weak language, not suited to implementation of really useful frameworks (unlike Python / Ruby) example : Find an ORM for PHP that is as good as sqlalchemy. It does not exist, because it is impossible to do. - really smart programmers dislike PHP because it is a pretty weak language, so they all flee to Python, Ruby, etc All big PHP applications turn into a huge usine à gaz, impossible to understand code, because of language weakness. - really smart DBAs dislike MySQL (unless they have a nice paying job at facebook or flickr) So, it is very difficult to find good PHP developers, and especially with database knowledge. IMHO, PostgreSQL has to be more flexible (in psychological terms) to understand MySQL user needs and answer them, just to give them a choice to migrate to PostgreSQL. Problem is, as you mentioned above, most PHP developers don't know what their needs are because they have little database expertise. About stuff MySQL does that I would like postgres to implement, I'd focus more on features, not syntax : - some form of index-only scans or equivalent (visibility map would probably suffice) - some form of INSERT ON DUPLICATE KEY UPDATE or equivalent (merge...) where the DB, not me, takes care of concurrency - some way to SELECT a,b,c,d GROUP BY a when it can be determined that it is equivalent to GROUP BY a,b,c,d, ie a is UNIQUE NOT NULL - index skip scans (well, MySQL doesn't really do index skip scans, but since it can do index-only scans, it's an approximation) - simpler syntax for DELETEs using JOINs And while I'm at it, I'll add my pet feature : An extremely fast form of temporary storage. Table main is referenced by tables child1, child2, ... childN - SELECT ... FROM main WHERE (very complex condition involving gist coordinates search etc) ORDER BY Then I want the rows from child tables which reference those results. If I add a lot of JOINs to my query, it's entirely possible that the (very complex condition involving gist coordinates search etc) is mis-estimated . This is generally not a problem since it usually uses bitmap index scans which can survive lots of abuse. However it causes mis-planning of the JOINs which is a problem. Besides, some of the child tables have few rows, but lots of columns, so it complicates the query and returns many times the same data, which the ORM doesn't care about since it would rather instanciate 1 object per referenced table row instead of 1 object per main table row. I would like to do : CREATE TEMP TABLE foo AS SELECT ... FROM main WHERE (very complex condition involving gist coordinates search etc); ANALYZE foo; SELECT * FROM foo ORDER BY ... SELECT c.* FROM foo JOIN child1 ON (...) SELECT c.* FROM foo JOIN child2 ON (...) etc This splits the query into much easier to manage fragments, and the results are easier to use, too. I can store in the application only 1 object per child table row. But I can't do this because it causes an update of system catalogs (slow, iowait, and bloat). Basically it would be nice to have something (temp table, cursor, CTE, tuplestore, whatever) that can hold a short-lived result set, can be used like a table, can have accurate statistics, and can be used in several queries, without disk writes. Note this would completely solve the
Re: [HACKERS] Core dump running PL/Perl installcheck with bleadperl [PATCH]
On Sun, Mar 07, 2010 at 12:11:26PM -0500, Tom Lane wrote: Tim Bunce tim.bu...@pobox.com writes: I encountered a core dump running PL/Perl installcheck with a very recent git HEAD of PostgreSQL and a not quite so recent git HEAD of perl. The cause is a subtle difference between SvTYPE(sv) == SVt_RV and SvROK(sv). The former is checking a low-level implementation detail while the later is directly checking does this sv contains a reference. Hmm. Seems like this patch begs the question: if checking SvTYPE(*svp) isn't safe, why is it safe to look at SvTYPE(SvRV(*svp))? Shouldn't the tests against SVt_PVHV be made more abstract as well? Some SvTYPE values, like SVt_RV, allow the SV to hold one of a number of different kinds of things. Others, like SVt_PVHV, don't. No, I don't like it either but that's the way the Jenga tower made of yaks (to use a phrase recently coined by one of the perl maintainers) has grown. Something like an SvRVHVOK(sv) would be welcome sugar. Tim. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On 2010-03-08 11:17 +0200, Pierre C wrote: - index skip scans (well, MySQL doesn't really do index skip scans, but since it can do index-only scans, it's an approximation) As far as I can tell, we already do index skip scans: = create index foo_a_b_idx on foo(a,b); CREATE INDEX = explain analyze select * from foo where b = 2; QUERY PLAN --- Index Scan using foo_a_b_idx on foo (cost=0.00..20.30 rows=5 width=8) (actual time=0.027..0.057 rows=1 loops=1) Index Cond: (b = 2) Total runtime: 0.075 ms (3 rows) Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
As far as I can tell, we already do index skip scans: This feature is great but I was thinking about something else, like SELECT DISTINCT, which currently does a seq scan, even if x is indexed. Here is an example. In both cases it could use the index to skip all non-interesting rows, pulling only 69 rows from the heap instead of 120K. EXPLAIN ANALYZE SELECT DISTINCT vente, type_id FROM annonces; QUERY PLAN --- HashAggregate (cost=15270.98..15271.82 rows=84 width=3) (actual time=113.277..113.288 rows=69 loops=1) - Seq Scan on annonces (cost=0.00..14682.32 rows=117732 width=3) (actual time=0.005..76.069 rows=119655 loops=1) EXPLAIN ANALYZE SELECT DISTINCT ON( vente, type_id ) * FROM annonces; QUERY PLAN Unique (cost=0.00..34926.90 rows=84 width=1076) (actual time=0.019..107.318 rows=69 loops=1) - Index Scan using annonces_type on annonces (cost=0.00..34338.24 rows=117732 width=1076) (actual time=0.017..52.982 rows=119655 loops=1) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On 2010-03-08 11:47 +0200, Pierre C wrote: As far as I can tell, we already do index skip scans: This feature is great but I was thinking about something else, like SELECT DISTINCT, which currently does a seq scan, even if x is indexed. Here is an example. In both cases it could use the index to skip all non-interesting rows, pulling only 69 rows from the heap instead of 120K. Oh, this is what I believe MySQL calls loose index scans. I'm actually looking into this as we speak, but there seems to be a non-trivial amount of work to be done in order for this to work. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql with GSS can crash
2010/3/7 Zdenek Kotala zdenek.kot...@sun.com: Magnus Hagander píše v po 01. 03. 2010 v 16:55 +0100: 2010/3/1 Zdenek Kotala zdenek.kot...@sun.com: Magnus Hagander píše v čt 25. 02. 2010 v 15:17 +0100: On Thu, Feb 25, 2010 at 15:04, Zdenek Kotala zdenek.kot...@sun.com wrote: Hi all, I got following stack: fd7ffed14b70 strlen () + 40 fd7ffed71665 snprintf () + e5 fd7fff36d088 pg_GSS_startup () + 88 fd7fff36d43a pg_fe_sendauth () + 15a fd7fff36e557 PQconnectPoll () + 3b7 fd7fff36e152 connectDBComplete () + a2 fd7fff36dc32 PQsetdbLogin () + 1b2 0041e96d main () + 30d 0041302c () It seems that connection is not fully configured and krbsrvname or pghost is not filled. Following code in fe-auth.c pg_GSS_startup() causes a crash: 440 maxlen = NI_MAXHOST + strlen(conn-krbsrvname) + 2; 441 temp_gbuf.value = (char *) malloc(maxlen); 442 snprintf(temp_gbuf.value, maxlen, %...@%s, 443 conn-krbsrvname, conn-pghost); 444 temp_gbuf.length = strlen(temp_gbuf.value); And following code in fe-connect.c fillPGconn() fill NULL value. 571 tmp = conninfo_getval(connOptions, krbsrvname); 572 conn-krbsrvname = tmp ? strdup(tmp) : NULL; I think that pg_GSS_startup should sanity the input. How did you get NULL in there? :-) There's a default set for that one that's PG_KRB_SRVNAM, so it really should never come out as NULL, I think... Yeah, you are right. conn-krbsrvname is postgres and conn-pghost is null Ah, good. We should defentd against that then. As for pghost, that certainly seems to be a bug. We check that one in krb5 and SSPI, but for some reason we seem to be missing it in GSSAPI. Yes. The check should be in GSSAPI too. However what I see in pg_hba.conf is following line: local all all gss Gss is used on local unix socket which probably cause a problem that conn-pghost is not filled when psql tries to connect. So there are really two errors - because we should disallow that. See attached patch - can you confirm it removes the crash with just the client side applied, and then that it properly rejects GSS with the server side applied as well? I tested it, but I cannot reproduce crash because I cannot setup illegal combination now ;-). I think it is OK. Ok, thanks for testing. I've been unable to break it in my testing as well so - applied, and back-patched. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?
2010/3/8 Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp: Tom Lane t...@sss.pgh.pa.us wrote: Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: I'd like to propose to define PGALWAYSEXPORT macro: #ifdef WIN32 #define PGALWAYSEXPORT __declspec (dllexport) #endif and modify PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1 to use it instead of PGDLLEXPORT. This seems like change for the sake of change. The existing mechanism works (as demonstrated by the fact that the contrib modules work on Windows). I wonder why the contrib modules can be compiled correctly because: 1. PG_MODULE_MAGIC requires dllexport. 2. Other exported variables from postgres requires dllimport. 3. Exported functions from the contrib DLLs require dllexport, but they don't have any PGDLLEXPORT tags in their functions. Did we use non-standard tools except msvc in the build frameword for core code? And what should I do for an external project? Yes, we use mingw. In this particular case, it may be the non-standard behavior that mingw exports *all* symbols in a DLL. We have some scripts in the MSVC build system that does this - it auto-generates a .DEF file that lists all symbols inthe file, and makes sure those are all exported. In fact, this even requires us to remove warnings created by modern versions of Visual Studio, since you're not supposed to use both dllexport and DEF files for the same symbol, but we do. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Oh, this is what I believe MySQL calls loose index scans. I'm Exactly : http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html actually looking into this as we speak, Great ! Will it support the famous top-n by category ? but there seems to be a non-trivial amount of work to be done in order for this to work. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication and privilege
On Fri, Mar 5, 2010 at 3:14 AM, Josh Berkus j...@agliodbs.com wrote: On 3/4/10 2:47 AM, Fujii Masao wrote: This TODO item really needs to be addressed for 9.0? Frankly I'm not familiar with that area, so I've not work on it at all yet, but I'm going to create the patch if many people want it for 9.0. What is your opinion? I think it falls under nice to have, but not essential for 9.0. Agreed. I moved it from TODO list for v9.0 to that for future release. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ecpg compiler warning about char* comparison
On Mon, Mar 08, 2010 at 10:46:03AM +0900, Takahiro Itagaki wrote: There is a complier warning in ecpg/ecpglib/error.c on HEAD: ... Instead, should we use if (strcmp(...) == 0) here? Exactly. Thanks for spotting this, fixed in HEAD. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing cvs HEAD - HS/SR - xlog timeline 0 pg_xlogfile_name_offset
On Thu, Feb 25, 2010 at 7:48 PM, Fujii Masao masao.fu...@gmail.com wrote:
On Thu, Feb 25, 2010 at 7:22 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Yeah. The current pg_*_last_location() functions don't cut it though,
you need to retain logs back to the redo location of the last
restartpoint. That's what %r returns. Maybe we should add another function?
+1
The attached patch introduces new function 'pg_last_checkpoint_start_location'
(better name?) that reports the XLOG location where the last checkpoint or
restartpoint started (i.e., the REDO starting location). This would be useful
to truncate the archived files to just the minimum required for recovery.
Is it worth applying this patch?
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 13048,13053 SELECT set_config('log_statement_stats', 'off', false);
--- 13048,13056
primarypg_current_xlog_insert_location/primary
/indexterm
indexterm
+ primarypg_last_checkpoint_start_location/primary
+/indexterm
+indexterm
primarypg_xlogfile_name_offset/primary
/indexterm
indexterm
***
*** 13110,13115 SELECT set_config('log_statement_stats', 'off', false);
--- 13113,13125
/row
row
entry
+ literalfunctionpg_last_checkpoint_start_location/function()/literal
+ /entry
+entrytypetext/type/entry
+entryGet transaction log location where last checkpoint started/entry
+ /row
+ row
+entry
literalfunctionpg_xlogfile_name_offset/function(parameterlocation/ typetext/)/literal
/entry
entrytypetext/, typeinteger//entry
***
*** 13186,13191 postgres=# select pg_start_backup('label_goes_here');
--- 13196,13211
/para
para
+ functionpg_last_checkpoint_start_location/ displays the transaction log location
+ where the last checkpoint (or restartpoint) started. The return location indicates
+ the earliest file that must be kept to allow the server to be restartable, so this
+ information can be used to truncate the archive to just the minimum required to
+ support restarting from the current database cluster. This function may be executed
+ during both recovery and in normal running. Also this function is read-only operation
+ and does not require superuser permission.
+/para
+
+para
You can use functionpg_xlogfile_name_offset/ to extract the
corresponding transaction log file name and byte offset from the results of any of the
above functions. For example:
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***
*** 8316,8321 pg_last_xlog_replay_location(PG_FUNCTION_ARGS)
--- 8316,8343
}
/*
+ * Report the last checkpoint (or restartpoint) start location
+ * (same format as pg_start_backup etc)
+ *
+ * This is useful for determining which archived WAL files are
+ * not required for the server and can be removed.
+ */
+ Datum
+ pg_last_checkpoint_start_location(PG_FUNCTION_ARGS)
+ {
+ XLogRecPtr recptr;
+ char location[MAXFNAMELEN];
+
+ LWLockAcquire(ControlFileLock, LW_SHARED);
+ recptr = ControlFile-checkPointCopy.redo;
+ LWLockRelease(ControlFileLock);
+
+ snprintf(location, sizeof(location), %X/%X,
+ recptr.xlogid, recptr.xrecoff);
+ PG_RETURN_TEXT_P(cstring_to_text(location));
+ }
+
+ /*
* Compute an xlog file name and decimal byte offset given a WAL location,
* such as is returned by pg_stop_backup() or pg_xlog_switch().
*
*** a/src/include/access/xlog_internal.h
--- b/src/include/access/xlog_internal.h
***
*** 268,273 extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS);
--- 268,274
extern Datum pg_current_xlog_insert_location(PG_FUNCTION_ARGS);
extern Datum pg_last_xlog_receive_location(PG_FUNCTION_ARGS);
extern Datum pg_last_xlog_replay_location(PG_FUNCTION_ARGS);
+ extern Datum pg_last_checkpoint_start_location(PG_FUNCTION_ARGS);
extern Datum pg_xlogfile_name_offset(PG_FUNCTION_ARGS);
extern Datum pg_xlogfile_name(PG_FUNCTION_ARGS);
extern Datum pg_is_in_recovery(PG_FUNCTION_ARGS);
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***
*** 3322,3327 DATA(insert OID = 3820 ( pg_last_xlog_receive_location PGNSP PGUID 12 1 0 0 f f
--- 3322,3329
DESCR(current xlog flush location);
DATA(insert OID = 3821 ( pg_last_xlog_replay_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_last_xlog_replay_location _null_ _null_ _null_ ));
DESCR(last xlog replay location);
+ DATA(insert OID = 3822 ( pg_last_checkpoint_start_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_last_checkpoint_start_location _null_ _null_ _null_ ));
+ DESCR(last
Re: [HACKERS] Safe security
On Wed, Mar 03, 2010 at 07:01:56PM -0500, Andrew Dunstan wrote:
Joshua D. Drake wrote:
On Wed, 2010-03-03 at 11:33 -0500, Andrew Dunstan wrote:
Well, we could put in similar weasel words I guess. But after
all, Safe's very purpose is to provide a restricted execution
environment, no?
We already do, in our license.
True. I think the weasel formula I prefer here is a bit different.
It might be reasonable to say something along the lines of:
To the extent it is prevented by the Perl Safe module, there is no
way provided to access internals of the database server process or
to gain OS-level access with the permissions of the server process,
as a C function can do.
Here's a patch that:
1. adds wording like that to the docs.
2. randomises the container package name (a simple and sound security measure).
3. requires Safe 2.25 (which has assorted fixes, including security).
4. removed a harmless but suprious exclamation mark from the source.
Tim.
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index c000463..0cc59c5 100644
*** a/doc/src/sgml/plperl.sgml
--- b/doc/src/sgml/plperl.sgml
*** $$ LANGUAGE plperl;
*** 856,862
operations that are restricted are those that interact with the
environment. This includes file handle operations,
literalrequire/literal, and literaluse/literal (for
!external modules). There is no way to access internals of the
database server process or to gain OS-level access with the
permissions of the server process,
as a C function can do. Thus, any unprivileged database user can
--- 856,864
operations that are restricted are those that interact with the
environment. This includes file handle operations,
literalrequire/literal, and literaluse/literal (for
!external modules). To the extent it is prevented by the Perl
!ulink url=http://search.cpan.org/perldoc?Safe;Safe/ulink module,
!there is no way provided to access internals of the
database server process or to gain OS-level access with the
permissions of the server process,
as a C function can do. Thus, any unprivileged database user can
diff --git a/src/pl/plperl/plc_safe_ok.pl b/src/pl/plperl/plc_safe_ok.pl
index ee2e33f..873143f 100644
*** a/src/pl/plperl/plc_safe_ok.pl
--- b/src/pl/plperl/plc_safe_ok.pl
*** if (not our $_init++) {
*** 52,58
# --- create and initialize a new container ---
$SafeClass ||= 'Safe';
! $PLContainer = $SafeClass-new('PostgreSQL::InServer::safe_container');
$PLContainer-permit_only(':default');
$PLContainer-permit(qw[:base_math !:base_io sort time require]);
--- 52,64
# --- create and initialize a new container ---
$SafeClass ||= 'Safe';
! # Give the container a random name to complicate an attack that needs the name
! # (Iff perl is loaded via shared_preload_libraries and perl uses the same
! # random function as postgres then perl's own seed function would have already
! # been called and an attacker could call the postgres setseed() before first
! # use of plperl to control the rand result. Even so, we try to make life hard.)
! # There's no known exploit based on this but it's cheap and wise.
! $PLContainer = $SafeClass-new('PostgreSQL::InServer::safe'.int(rand(time+$^T+$!)));
$PLContainer-permit_only(':default');
$PLContainer-permit(qw[:base_math !:base_io sort time require]);
*** sub safe_eval {
*** 91,95
}
sub mksafefunc {
! ! return safe_eval(PostgreSQL::InServer::mkfuncsrc(@_));
}
--- 97,101
}
sub mksafefunc {
! return safe_eval(PostgreSQL::InServer::mkfuncsrc(@_));
}
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 956eddb..a834063 100644
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
*** plperl_trusted_init(void)
*** 691,702
safe_version_x100 = (int) (SvNV(safe_version_sv) * 100);
/*
! * Reject too-old versions of Safe and some others: 2.20:
! * http://rt.perl.org/rt3/Ticket/Display.html?id=72068 2.21:
! * http://rt.perl.org/rt3/Ticket/Display.html?id=72700
*/
! if (safe_version_x100 209 || safe_version_x100 == 220 ||
! safe_version_x100 == 221)
{
/* not safe, so disallow all trusted funcs */
eval_pv(PLC_SAFE_BAD, FALSE);
--- 691,699
safe_version_x100 = (int) (SvNV(safe_version_sv) * 100);
/*
! * Reject too-old versions of Safe
*/
! if (safe_version_x100 225)
{
/* not safe, so disallow all trusted funcs */
eval_pv(PLC_SAFE_BAD, FALSE);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Explicit psqlrc
On Mon, Mar 8, 2010 at 1:39 AM, David Christensen da...@endpoint.com wrote: On Mar 7, 2010, at 9:22 AM, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: 2010/3/6 Tom Lane t...@sss.pgh.pa.us: The analogy I was thinking about was psql -X, but I agree that it's not obvious why this shouldn't be thought of as an additional -f file. Uh, I don't follow. When we use -f, we'll run the script and then exit. The whole point is to run it and *not* exit, since you are normally using it to set up the environment in psql. If we were going to support multiple -f options, it would be sensible to interpret -f - as read from stdin until EOF. Then you could interleave prepared scripts and stdin, which could be pretty handy. The default behavior would be equivalent to a single instance of -f -, and what you are looking for would be -X -f substitute-psqlrc -f -. Here's an initial stab at supporting multiple -f's (not counting the interpretation of -f - as STDIN). There are also a few pieces that are up for interpretation, such as the propagation of the return value of the MainLoop(). Also, while this patch supports the single-transaction mode, it does so in a way that will break if one of the scripts include explicit BEGIN/COMMIT statements (although it is no different than the existing code in this regard). I have added to this to the next CommitFest. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 4:58 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-03-08 11:47 +0200, Pierre C wrote: As far as I can tell, we already do index skip scans: This feature is great but I was thinking about something else, like SELECT DISTINCT, which currently does a seq scan, even if x is indexed. Here is an example. In both cases it could use the index to skip all non-interesting rows, pulling only 69 rows from the heap instead of 120K. Oh, this is what I believe MySQL calls loose index scans. I'm actually looking into this as we speak, but there seems to be a non-trivial amount of work to be done in order for this to work. We should probably have a TODO for this, if we don't already. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
+1 Isn´t that a good time to think to put that question into the list of things PostgreSQL doesn´t want to do? Cheers Von: Andrew Dunstan and...@dunslane.net An: francois.pe...@free.fr CC: Josh Berkus j...@agliodbs.com; Craig Ringer cr...@postnewspapers.com.au; pgsql-hackers@postgresql.org; dp...@pgadmin.org Gesendet: Samstag, den 6. März 2010, 22:01:06 Uhr Betreff: Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL François Pérou wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. This is just fantasy. Doing this will destabilize Postgres, cost us hugely in maintenance effort and LOSE us users. If we do this why the heck should we stop there? Why shouldn't we replicate the broken behaviour of every major database out there? It's really time for you to stop making this suggestion, once and for all. It is just not going to happen. Moreover MySQL appears to be fracturing into a bunch of different forks, so why now, of all times, would we want to adopt its broken syntax? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers __ Do You Yahoo!? Sie sind Spam leid? Yahoo! Mail verfügt über einen herausragenden Schutz gegen Massenmails. http://mail.yahoo.com
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 9:48 AM, Wolfgang Wilhelm wolfgang20121...@yahoo.de wrote: +1 Isn´t that a good time to think to put that question into the list of things PostgreSQL doesn´t want to do? Yes. ...Robert Von: Andrew Dunstan and...@dunslane.net François Pérou wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. This is just fantasy. Doing this will destabilize Postgres, cost us hugely in maintenance effort and LOSE us users. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Robert Haas robertmh...@gmail.com wrote: Wolfgang Wilhelm wolfgang20121...@yahoo.de wrote: Isn*t that a good time to think to put that question into the list of things PostgreSQL doesn*t want to do? Yes. Done. http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: Wolfgang Wilhelm wolfgang20121...@yahoo.de wrote: Isn*t that a good time to think to put that question into the list of things PostgreSQL doesn*t want to do? Yes. Done. http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want Does this conflict conceptually with the item from Exotic Features on the same page?: * Add pre-parsing phase that converts non-ISO syntax to supported syntax This could allow SQL written for other databases to run without modification. Regards, David -- David Christensen End Point Corporation da...@endpoint.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Yes, I've seen quite a few of François's posts around on Drupal. Drupal 7 has an OO query building abstraction layer which _should_ address most of the issues and differences between MySQL, PostgreSQL and SQLite (newly supported in Drupal 7) because each driver can form the query string specific for the database it supports. That leaves Drupal core fairly well supported. On issue that does still remain though is casting. There is no abstraction around most functions such as CAST. CAST(1234 as TEXT) in PostgreSQL is CAST(1234 as CHAR) in MySQL - its an edge case, but Drupal being a PHP app - its likely to come up often. Aside from Drupal core, its too soon to know if there will be problems with D7 contrib but there are a few major bugs about other D6 contrib modules. The biggest one, which I think need movement to get fixed is in the Views module. The Views module has a bit of a hard time trying to please both databases and its surrounding the use of DISTINCT and restricting duplicate results. They've opted for a solution that really hits hard on PostgreSQL's performance. Bascially, when a DISTINCT clause is used, all other fields being selected get a custom functional called FIRST rapped around them: SELECT DISTINCT(nid), FIRST(title), FIRST(body), . The function merely returns the first value when two values are present for that row. This is the alternate instead of grouping by each field. Its stupid and needs to be fixed. The issue is here: http://drupal.org/node/460838 Josh Waihi - Drupal PostgreSQL Maintainer Mark Kirkwood wrote: François Pérou wrote: I will continue using PostgreSQL and MySQL user base will continue to grow and one day it will be 1 PostgreSQL user for 1.000 MySQL users. This is life. People have a deep psychological addiction to their believes and ideas. IMHO, PostgreSQL has to be more flexible (in psychological terms) to understand MySQL user needs and answer them, just to give them a choice to migrate to PostgreSQL. All your discussions are about technical things and you think I make fun of Drupal developers. I only tried to point out psychological believes, which we have to understand to answer their needs and grow PostgreSQL user base. I think the Drupal developers are addressing the main thrust of your concerns - one of the gentlemen I work with here at Catalyst (Josh Waihi) has spent considerable time working on Postgresql issues for Drupal 7. Last time I checked, Drupal 7 + Postgresql passes most of the regression tests. Maybe you could consider helping out making Drupal 7 + Postgresql pass the remaining ones? regards Mark -- Joshua Waihi // Drupal Architect Catalyst.Net Limited, Level 6, Catalyst House, 150 Willis Street, Wellington. P.O.Box 11053, Manners Street, Wellington 6142 DDI: +64 4 803 2228 Mob: +64 21 979 794 Tel: +64 4 499 2267 Web: http://catalyst.net.nz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
David Christensen da...@endpoint.com writes: On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote: Wolfgang Wilhelm wolfgang20121...@yahoo.de wrote: Isn*t that a good time to think to put that question into the list of things PostgreSQL doesn*t want to do? Done. http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want Does this conflict conceptually with the item from Exotic Features on the same page?: * Add pre-parsing phase that converts non-ISO syntax to supported syntax This could allow SQL written for other databases to run without modification. I think the new item might be phrased a little too broadly. The problem with mysql's GROUP BY behavior is not the syntax but the nonstandard semantics, ie, that it will pick a random result row when the query is underspecified. That means you can't just do a syntax translation, which is what the exotic wishlist item seems to be envisioning. I believe what that's actually about is the idea of converting things like Oracle's CONNECT BY into SQL-spec constructs. Doing so wouldn't break any existing PG-compatible applications, whereas messing with the semantics of GROUP BY probably would. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
Tim Bunce tim.bu...@pobox.com writes: Here's a patch that: 1. adds wording like that to the docs. 2. randomises the container package name (a simple and sound security measure). 3. requires Safe 2.25 (which has assorted fixes, including security). 4. removed a harmless but suprious exclamation mark from the source. #3 is still an absolute nonstarter, especially for a patch that we'd wish to backpatch. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Tom Lane t...@sss.pgh.pa.us wrote: I think the new item might be phrased a little too broadly. The problem with mysql's GROUP BY behavior is not the syntax but the nonstandard semantics, ie, that it will pick a random result row when the query is underspecified. I thought that some of the items on the OP's list were requests to add an alternative syntax for an existing feature, without a change in semantics. Did I misunderstand that? If not, is it something we want to consider? I do know that some of the requests were to support behavior we would consider incorrect (like the non-deterministic results from an underspecified GROUP BY); not only do we not want to go to any effort to *add* it, but we'd probably be putting in effort to *eliminate* it if it was present. Should the TODO list not wanted section explicitly list each such feature, so that non-listed features aren't painted by the same broad brush? I believe what that's actually about is the idea of converting things like Oracle's CONNECT BY into SQL-spec constructs. Doing so wouldn't break any existing PG-compatible applications, whereas messing with the semantics of GROUP BY probably would. Yeah, my first draft of that was even broader, not naming MySQL in particular -- but then I remembered that we've made a few concessions to Oracle compatibility. As far as I can recall, though, those tend not to involve new syntax, but functions that aren't required by the standard -- which seems much less invasive than the OP's requests. I'm willing to rework, soften, or narrow the entry as needed, and I certainly would take no offense at anyone else doing so. I was just trying to get it listed, since there seemed to be some community consensus on the point. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
On Mar 8, 2010, at 8:03 AM, Tom Lane wrote: #3 is still an absolute nonstarter, especially for a patch that we'd wish to backpatch. You're at least going to want to exclude Safe 2.20 - 2.23, IIUC. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@peufeu.com wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. This sounds like a better idea... PHP developers don't have time to invest in learning deep SQL. This is true, and it is a big problem IMHO. It results in lots of slow, broken, insecure database designs. So, if php dev doesn't have time to learn to do things right then we have to find time to learn to do things wrong? seems like a nosense argument to me -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@peufeu.com wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. This sounds like a better idea... Aside from that little halting problem issue, it sounds wonderful. You do know that SQL is Turing-complete, right? PHP developers don't have time to invest in learning deep SQL. This is true, and it is a big problem IMHO. It results in lots of slow, broken, insecure database designs. So, if php dev doesn't have time to learn to do things right then we have to find time to learn to do things wrong? seems like a nosense argument to me Indeed. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/9 Jaime Casanova jcasa...@systemguards.com.ec: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@peufeu.com wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. This sounds like a better idea... Could parser rewriter hook be another solution here? I'm completely against the wrong GROUP BY syntax from MySQL, but it is also true that SQL is only an interface. -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
David E. Wheeler da...@kineticode.com writes: On Mar 8, 2010, at 8:03 AM, Tom Lane wrote: #3 is still an absolute nonstarter, especially for a patch that we'd wish to backpatch. You're at least going to want to exclude Safe 2.20 - 2.23, IIUC. If those aren't versions that are likely to be in wide use, no objection to that. I'm just concerned about arbitrarily breaking existing installations. I note that Fedora 11 and OS X 10.6.2 are providing Safe 2.12, which means the proposed patch would break plperl on every machine I have, without easy recourse --- I am not likely to install a private version of Safe under either OS, and I doubt many other PG users would wish to either. The net effect would be to prevent PG users from upgrading until the OS vendors get around to issuing new versions, which is not helpful. Particularly if the vendor chooses to back-patch Safe security fixes without bumping the visible version number, as is not unlikely for Red Hat in particular. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 12:10 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@peufeu.com wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. This sounds like a better idea... Aside from that little halting problem issue, it sounds wonderful. You do know that SQL is Turing-complete, right? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
On Mon, Mar 08, 2010 at 11:03:27AM -0500, Tom Lane wrote: Tim Bunce tim.bu...@pobox.com writes: Here's a patch that: 1. adds wording like that to the docs. 2. randomises the container package name (a simple and sound security measure). 3. requires Safe 2.25 (which has assorted fixes, including security). 4. removed a harmless but suprious exclamation mark from the source. #3 is still an absolute nonstarter, especially for a patch that we'd wish to backpatch. This is a patch for 9.0. Backpatching is a separate issue. I think Safe 2.25 should be required, but I'll let whoever applies the patch tweak/delete that hunk as desired. Tim. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
On Mar 8, 2010, at 9:14 AM, Tom Lane wrote: If those aren't versions that are likely to be in wide use, no objection to that. Yes, those are a series of releases in the last couple of months that had one level of brokenness or another I'm going to test 2.25 today. I'm just concerned about arbitrarily breaking existing installations. I note that Fedora 11 and OS X 10.6.2 are providing Safe 2.12, which means the proposed patch would break plperl on every machine I have, without easy recourse --- I am not likely to install a private version of Safe under either OS, and I doubt many other PG users would wish to either. The net effect would be to prevent PG users from upgrading until the OS vendors get around to issuing new versions, which is not helpful. Agreed, older ones should be allowed; the Perl community should recommend that everyone upgrade to get improved security, but it shouldn't be required. Particularly if the vendor chooses to back-patch Safe security fixes without bumping the visible version number, as is not unlikely for Red Hat in particular. This is why I hate packaging systems. Frankly, Red Hat's Perl has been consistently broken for close to a decade, mainly because of patching practices such as this. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 08, 2010 at 12:18:31PM -0500, Robert Haas wrote: On Mon, Mar 8, 2010 at 12:10 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@peufeu.com wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. This sounds like a better idea... Aside from that little halting problem issue, it sounds wonderful. You do know that SQL is Turing-complete, right? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. MySQL's SQL isn't Turing complete. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
2010/3/8 David E. Wheeler da...@kineticode.com: Particularly if the vendor chooses to back-patch Safe security fixes without bumping the visible version number, as is not unlikely for Red Hat in particular. This is why I hate packaging systems. Frankly, Red Hat's Perl has been consistently broken for close to a decade, mainly because of patching practices such as this. Goes both way - it's the main reason I hate CPAN, and I know many sysadmins who hold just that position. (to be clear: the lack of back-branch management on CPAN is what sucks) But we're not arguing that. We know it's a situation out there, and we jus thave to deal with it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 12:43 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 12:18:31PM -0500, Robert Haas wrote: On Mon, Mar 8, 2010 at 12:10 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@peufeu.com wrote: My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. A solution would be a SQL proxy (a la pgpool) with query rewriting. This sounds like a better idea... Aside from that little halting problem issue, it sounds wonderful. You do know that SQL is Turing-complete, right? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. MySQL's SQL isn't Turing complete. It still doesn't matter. Turing-completeness does not preclude syntax transformation. Non-Turing completeness, even less so. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Robert Haas wrote: You do know that SQL is Turing-complete, right? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. If we were engaged in an academic exercise this might be interesting. But we aren't. The fact that one can do something is not an argument for actually doing it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
On Mon, Mar 8, 2010 at 09:03, Tom Lane t...@sss.pgh.pa.us wrote: Tim Bunce tim.bu...@pobox.com writes: 3. requires Safe 2.25 (which has assorted fixes, including security). #3 is still an absolute nonstarter, especially for a patch that we'd wish to backpatch. FWIW I think its a given you probably always want the latest version of X or Y. I mean what happens when Safe 2.26 comes out and fixes more issues? We blacklist 2.25? Seems like a PITA. Why not just have something in the docs about keeping your stuff up2date? That being said I would be in favor of at least saying Hey! your using a known broken version of Safe. Maybe something like the below at pl_perl init time? (That is instead of requiring v2.25 just complain about older versions) elog(WARNING, Safe versions before 2.25 have known issues. Please consider upgrading.); Thoughts? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
Alex Hunsaker bada...@gmail.com writes: That being said I would be in favor of at least saying Hey! your using a known broken version of Safe. Maybe something like the below at pl_perl init time? (That is instead of requiring v2.25 just complain about older versions) elog(WARNING, Safe versions before 2.25 have known issues. Please consider upgrading.); We're in the service of providing a tool, not a nanny. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Kevin Grittner kevin.gritt...@wicourts.gov writes: I thought that some of the items on the OP's list were requests to add an alternative syntax for an existing feature, without a change in semantics. Did I misunderstand that? If not, is it something we want to consider? I think the pre-existing TODO item is evidence that there's at least willingness to consider such things. (OTOH I believe that item has been there for quite a long time, without any action being taken.) I do know that some of the requests were to support behavior we would consider incorrect (like the non-deterministic results from an underspecified GROUP BY); not only do we not want to go to any effort to *add* it, but we'd probably be putting in effort to *eliminate* it if it was present. Should the TODO list not wanted section explicitly list each such feature, so that non-listed features aren't painted by the same broad brush? Yes, I think we should narrowly list things we don't want to do. The current wording reads like we aren't interested in adopting any MySQL ideas, which I don't think is actually the project consensus, not to mention that it doesn't look good from a PR standpoint. I believe we do have consensus that we aren't interested in adopting MySQL's nonstandard GROUP BY semantics. I don't recall what else there might be a definite no for. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
So, if php dev doesn't have time to learn to do things right then we have to find time to learn to do things wrong? seems like a nosense argument to me The best ever reply I got from phpBB guys on I don't remember which question was : WE DO IT THIS WAY BECAUSE WE WANT TO SUPPORT MYSQL 3.x You can frame this and put it on your wall. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
On Mon, Mar 8, 2010 at 10:14, Tom Lane t...@sss.pgh.pa.us wrote: David E. Wheeler da...@kineticode.com writes: On Mar 8, 2010, at 8:03 AM, Tom Lane wrote: #3 is still an absolute nonstarter, especially for a patch that we'd wish to backpatch. You're at least going to want to exclude Safe 2.20 - 2.23, IIUC. If those aren't versions that are likely to be in wide use, no objection to that. I'm just concerned about arbitrarily breaking existing installations. Here are a few version numbers for released perls: perl | safe version 5.8.8 | 2.12 5.8.9 | 2.16 5.10.0 | 2.12 5.10.1 | 2.18 5.12 looks like it will release with 2.25, 5.10.2 if it ever gets released is currently at 2.18, 5.8.10 does not even seem to be on the horizon. So unless you installed a private version or your distro is providing updates (I looked at: arch, debian, fedora and openbsd. And they don't seem to.) it seems unlikely to see 2.18 in the wild. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Tom Lane t...@sss.pgh.pa.us wrote: I believe we do have consensus that we aren't interested in adopting MySQL's nonstandard GROUP BY semantics. I don't recall what else there might be a definite no for. TODO not wanted entry rewritten to address just this one issue. The other issues raise in the original post are valid possible enhancements, or is there something else to list?: http://archives.postgresql.org/pgsql-hackers/2010-03/msg00257.php -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Explicit psqlrc
On Sun, 2010-03-07 at 16:37 +0100, Magnus Hagander wrote: With your interleave, you mean things like psql -f first.sql -f - -f second.sql? That does sound like it could be handy - and also really dangerous :-) Multiple -f support would be a good thing. As would mixed -f and -c options. What would be even better would be bringing across many of the concepts that are in pgbench, which has had the multiple file support for some time. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Tom Lane wrote: Yes, I think we should narrowly list things we don't want to do. The current wording reads like we aren't interested in adopting any MySQL ideas, which I don't think is actually the project consensus, not to mention that it doesn't look good from a PR standpoint. Indeed. We are always open to good ideas, I hope. The really obvious candidate of missing functionality from MySQL hasn't even been mentioned in this thread, AFAIK: some form of insert_or_update. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] disabling log_min_duration_statement from pg_dump?
Hi, Every often, i analyze logs to find most slow queries... and every time i found myself trying to ignore pg_dump's generated COPY commands which could be very expensive... can we add a parameter in pg_dump and pg_dumpall to disable log_min_duration_statement? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Kevin Grittner kevin.gritt...@wicourts.gov writes: TODO not wanted entry rewritten to address just this one issue. The other issues raise in the original post are valid possible enhancements, or is there something else to list?: http://archives.postgresql.org/pgsql-hackers/2010-03/msg00257.php I'm not too sure either way about the other items mentioned there. But anyway the GROUP BY business is the only one that seems to come up often enough to justify an explicit no listing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] disabling log_min_duration_statement from pg_dump?
Jaime Casanova jcasa...@systemguards.com.ec writes: can we add a parameter in pg_dump and pg_dumpall to disable log_min_duration_statement? export PGOPTIONS=-c log_min_duration_statement=-1 Or use ALTER USER SET to add that to the options for the account you do pg_dump with; etc etc. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] disabling log_min_duration_statement from pg_dump?
On Mon, Mar 8, 2010 at 4:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: can we add a parameter in pg_dump and pg_dumpall to disable log_min_duration_statement? export PGOPTIONS=-c log_min_duration_statement=-1 ah! so, that is PGOPTIONS for :) and that works for every libpq application? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] invalid UTF-8 via pl/perl
On Sat, 2010-01-02 at 20:51 -0500, Andrew Dunstan wrote:
Andrew Dunstan wrote:
I think the plperl glue code should check returned strings using
pg_verifymbstr().
Please test this patch. I think we'd probably want to trap the encoding
error and issue a customised error message, but this plugs all the holes
I can see with the possible exception of values inserted via SPI calls.
I'll check that out.
I got a report, that the patch fixes one case but leaves open another:
CREATE TABLE utf_test
(
id serial PRIMARY KEY,
data character varying
);
CREATE OR REPLACE FUNCTION utf_test()
RETURNS character varying AS
$BODY$
return \xd0;
$BODY$
LANGUAGE 'plperlu' VOLATILE STRICT;
CREATE OR REPLACE FUNCTION utf_test2()
RETURNS character varying AS
$BODY$
spi_exec_query(insert into utf_test (data) values('\xd0'););
return VIGA;
$BODY$
LANGUAGE 'plperlu' VOLATILE STRICT;
The report siad, that patch fixes case
insert into utf_test (data) values(utf_test());
so that it return an error, but the second function
select utf_test2();
still enters wrong data to the table
So SPI interface should also be fixed, either from perl side, or maybe
from inside SPI ?
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] disabling log_min_duration_statement from pg_dump?
Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: can we add a parameter in pg_dump and pg_dumpall to disable log_min_duration_statement? export PGOPTIONS=-c log_min_duration_statement=-1 ah! so, that is PGOPTIONS for :) and that works for every libpq application? Yes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Bruce Momjian wrote: BBruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: The attached patch checks for the proper return from BEGIN/COMMIT, and properly frees the libpq structures. In testing, this does return 3 as you expected. Really? It looks to me like you'd get exit(1). Maybe that's the right thing, but MainLoop itself seems to return EXIT_USER not EXIT_FAILURE when it gets an error. Sorry, you are right. I must have mis-read my tests. Updated patch attached. I thought some more about it and realized I had to check for the on-error-exit flag too. Updated patch attached. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Bruce Momjian br...@momjian.us writes: I thought some more about it and realized I had to check for the on-error-exit flag too. Updated patch attached. Applied. Shouldn't that be back-patched? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] invalid UTF-8 via pl/perl
Hannu Krosing ha...@2ndquadrant.com writes: So SPI interface should also be fixed, either from perl side, or maybe from inside SPI ? SPI has every right to assume that data it's given is already in the database encoding. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I thought some more about it and realized I had to check for the on-error-exit flag too. Updated patch attached. Applied. Shouldn't that be back-patched? Uh, well, it is going to change the behavior of back branches, and because we only got one report of the bug which has existed since 8.2, I didn't want to risk it. Should I? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Shouldn't that be back-patched? Uh, well, it is going to change the behavior of back branches, and because we only got one report of the bug which has existed since 8.2, I didn't want to risk it. Should I? I would say that the odds of the initial BEGIN failing are negligible anyway, so what it boils down to is whether a failure on the final COMMIT needs to be reported. Seems to me the answer is yes, and the only reason we haven't had more complaints is that not too many people have actually relied on the exit status. Anyone who *does* look at the exit status is not going to be happy with the current behavior. In short: it's a bug, fix it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable pg_controldata?
On 03/05/2010 10:28 AM, Greg Smith wrote: Heikki Linnakangas wrote: Then again, if you don't use the copy in shared memory but just open the pg_control file and read it in the UDF, you could implement this as a pgfoundry module that works with older versions too. This is the direction I'd prefer to see this go in a 9.0 context. It's easy enough to build a fully functional version that lives works via the same proposed final UDF interface, just with the extra step of reading the file. Get that working, and you just added a useful module supporting all the way back to 8.2 (I think--not sure if there's been any other changes that would break this) that people would love to have. FWIW, here is a quick and dirty effort: http://www.joeconway.com/source_code/pg_controldata.tar.gz It turns out some minor changes are needed for 8.4 or earlier as some attributes have been added to ControlFileData. Once we get general agreement I can do that too. I have not bothered to start a pgfoundry project yet -- thoughts? Joe signature.asc Description: OpenPGP digital signature
Re: [HACKERS] machine-readable pg_controldata?
On Sat, Mar 6, 2010 at 12:13 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The REDO location of last checkpoint might deserve a function of its own, like we have pg_last_xlog_replay_location() and pg_last_xlog_receive_location(). Agreed. I submitted the patch which introduces new function returning the REDO location of last checkpoint, on other thread. http://archives.postgresql.org/pgsql-hackers/2010-03/msg00334.php Are you planning to apply the patch until 9.0 release? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Shouldn't that be back-patched? Uh, well, it is going to change the behavior of back branches, and because we only got one report of the bug which has existed since 8.2, I didn't want to risk it. Should I? I would say that the odds of the initial BEGIN failing are negligible anyway, so what it boils down to is whether a failure on the final COMMIT needs to be reported. Seems to me the answer is yes, and the only reason we haven't had more complaints is that not too many people have actually relied on the exit status. Anyone who *does* look at the exit status is not going to be happy with the current behavior. In short: it's a bug, fix it. OK, done. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] lock mode for ControlFileLock which pg_start_backup uses
Hi, Currently pg_start_backup() accesses the shared ControlFile by using ControlFileLock with LW_EXCLUSIVE lock mode. But since that access is read-only operation, LW_SHARED should be chosen instead of LW_EXCLUSIVE. The attached patch changes the lock mode which pg_start_backup() uses. Is it worth applying this patch? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 7897,7903 pg_start_backup(PG_FUNCTION_ARGS) * REDO pointer. The oldest point in WAL that would be needed to * restore starting from the checkpoint is precisely the REDO pointer. */ ! LWLockAcquire(ControlFileLock, LW_EXCLUSIVE); checkpointloc = ControlFile-checkPoint; startpoint = ControlFile-checkPointCopy.redo; LWLockRelease(ControlFileLock); --- 7897,7903 * REDO pointer. The oldest point in WAL that would be needed to * restore starting from the checkpoint is precisely the REDO pointer. */ ! LWLockAcquire(ControlFileLock, LW_SHARED); checkpointloc = ControlFile-checkPoint; startpoint = ControlFile-checkPointCopy.redo; LWLockRelease(ControlFileLock); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] invalid UTF-8 via pl/perl
Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: So SPI interface should also be fixed, either from perl side, or maybe from inside SPI ? SPI has every right to assume that data it's given is already in the database encoding. Yeah, looks like we missed a few spots. I have added three more checks that I think plug the remaining holes in plperl. Hannu, please test again against CVS HEAD. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lock mode for ControlFileLock which pg_start_backup uses
Fujii Masao masao.fu...@gmail.com wrote: Currently pg_start_backup() accesses the shared ControlFile by using ControlFileLock with LW_EXCLUSIVE lock mode. But since that access is read-only operation, LW_SHARED should be chosen instead of LW_EXCLUSIVE. Almost all operations of ControlFileLock is in LW_EXCLUSIVE, but there is one usage of LWLockConditionalAcquire(ControlFileLock, LW_SHARED) in XLogNeedsFlush(). The attached patch changes the lock mode which pg_start_backup() uses. Is it worth applying this patch? I think the patch is reasonable to represent what we are doing, even if there is no performance benefits from it. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?
Magnus Hagander mag...@hagander.net wrote: The existing mechanism works (as demonstrated by the fact that the contrib modules work on Windows). Did we use non-standard tools except msvc in the build framework for core code? And what should I do for an external project? Yes, we use mingw. Hmmm, it means the existing mechanism can only work on limited environments. Should we make the code to be more portable for standard developers? Third party developer might not build the postgres server, but they would want to build their extension modules without mingw. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
