Re: [GENERAL] pgAdmin3 not working with Gnome3
On Thu, 1 Sep 2011 21:12:59 -0700, Mike Christensen m...@kitchenpc.com wrote: Hi all - I'm on openSuse running the latest stable release of Gnome3 (Just trying it out, so far the fact I can't minimize windows is perhaps more than my old school brain can handle).. I've noticed in pgAdmin, basically no popup works. If I right click on the Databases branch and select New Database, nothing happens. If I right click on the Tables brand and select New Table, nothing happens.. I know for a fact this works when I logon with KDE.. If anyone wants more info from my system, just lemme know what I can do to get that for you.. Thanks!! Okay good news is that these menus do come up, bad news is the bug is even more obscure. Here's my setup: I have two monitors. Gnome3 allows you to create multiple desktops to organize your windows, however this only affects your primary monitor. I actually kinda like this, since I have my web browser on my second monitor and I can scroll through apps on my primary monitor. In this case, I had pgAdmin on my second monitor and some random app, like Eclipse on the primary monitor. I selected Create Database on pgAdmin and apparently nothing happened. What it actually did it spawned the new dialog on my primary monitor BUT on another virtual desktop. I finally noticed it when I flipped over to that one.. pgAdmin displays the dialog where it was the last time you opened it. AFAICT, it works with multiple monitors. We don't set any virtual desktop. I also use Gnome 3, and never saw incompatibilities between this window manager and pgAdmin3. I guess the bug can be fixed as: If the bug is in pgAdmin3... 1) Always spawn new child windows on the same monitor. or 2) In Gnome 3, create the window on the virtual desktop that's currently active.. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Missing DLL after unplaned server stop
Hi everybody! I've encountered a problem similar to this mentioned on this thread: http://archives.postgresql.org/pgsql-bugs/2009-12/msg00207.php I've got the same problem: I'm unable to start PostGreSQL (trough PGAdmin or the service itself). Message is: This application failed to start because SSLEAY32.dll was not found. Re-installing the application may fix this problem. This problem appeared after an unwilled server stop (because of a storm). Nevertheless, I can't be sure my problem is related to this stop. My server is a Windows Server 2008 64 bit. I'm using a PostGreSQL 9.0.4 version (32 bit), with PostGIS extension 1.5. So, I looked after the missing dll: I found it into the folder ..\PostgreSQL\9.0\bin\postgisgui. I didn't try to reinstall PostGreSQL because I have some data that I would like to get back. Nevertheless, I haven't done any database backup... Is there any way to get back data without backup? Or maybe is there a way to solve the dll problem without reinstalling PostGreSQL? Any help would be great! :) Geoffrey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] md5 of table
On Thursday 01 September 2011 11:47:24 Sim Zacks wrote: Is there a way to get an md5 or other hash of an entire table? I want to be able to easily compare 2 tables in different databases. I thought about using dblink and the EXCEPT query, but then I need to know the field list of each query result, which is a pain in the butt. If I could return an md5 of the entire table, then I could check if the tables have the same hash and be confident enough that the tables were identical. Thanks Sim You might also want to take a look at http://pgfoundry.org/projects/pg-comparator/ which can give a more nuanced view of db differences and tries to be smart about performance. It looks a bit stale; I haven't used it in ages, but it used to be a trusty part of our test suite. -- Vincent de Phily -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE using query; per-row function calling problem
On 02/09/11, Tom Lane (t...@sss.pgh.pa.us) wrote: Rory Campbell-Lange r...@campbell-lange.net writes: I'm doing an UPDATE something like this: UPDATE slots SET a = 'a' ,b = (SELECT uuid_generate_v1()) WHERE c = TRUE; Each updated row in slots is getting the same value for b. That's Postgres' interpretation of an uncorrelated sub-SELECT: there's no reason to do it more than once, so it doesn't. Is there a way of getting a per-row value from uuid_generate_v1() without doing a PL loop? Drop the word SELECT. Why did you put that in in the first place? Hi Tom Good question to which I don't know the answer. Thanks very much for the advice. I was able to force a per-row call to uuid_generate_v1 by using this pattern UPDATE r_slots SET b = (SELECT y.x FROM (select -1 as n, uuid_generate_v1() as x )y WHERE y.n != r_slots.id) ... But b = uuid_generate_v1() is a lot simpler! In my -1 example, am I right in assuming that I created a correlated subquery rather than an correlated one? I'm confused about the difference. Many thanks Rory -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Missing DLL after unplaned server stop
So, some following for people who will go trough this problem! I tried many things to solve this problem, and after a while, I noticed that a lot of DLL were missing... Completely hopeless, I decided to erase my installed PostGreSQL version with fresh binaries downloaded at this adress: http://www.postgresql.org/download/windows Unbelievable: everything has come back! Even my data, because I let their folder untouched. So, sometimes, just be brutal... I think, the problem came from a windows update. I found some posts related to the same problem, like this one: http://forums.enterprisedb.com/posts/list/2843.page Geoffrey On Fri, 02 Sep 2011 10:09:02 +0200, gbrun gb...@myopera.com wrote: Hi everybody! I've encountered a problem similar to this mentioned on this thread: http://archives.postgresql.org/pgsql-bugs/2009-12/msg00207.php I've got the same problem: I'm unable to start PostGreSQL (trough PGAdmin or the service itself). Message is: This application failed to start because SSLEAY32.dll was not found. Re-installing the application may fix this problem. This problem appeared after an unwilled server stop (because of a storm). Nevertheless, I can't be sure my problem is related to this stop. My server is a Windows Server 2008 64 bit. I'm using a PostGreSQL 9.0.4 version (32 bit), with PostGIS extension 1.5. So, I looked after the missing dll: I found it into the folder ..\PostgreSQL\9.0\bin\postgisgui. I didn't try to reinstall PostGreSQL because I have some data that I would like to get back. Nevertheless, I haven't done any database backup... Is there any way to get back data without backup? Or maybe is there a way to solve the dll problem without reinstalling PostGreSQL? Any help would be great! :) Geoffrey -- Using Opera's revolutionary email client: http://www.opera.com/mail/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory leak somewhere at PQconnectdb?
Hi all, I now know it's somewhat an academic exercise of little practical importance, thanks for the clarification!! Cheers, Antonio 2011/9/2 Tom Lane t...@sss.pgh.pa.us: Craig Ringer ring...@ringerc.id.au writes: Even better, add a valgrind suppressions file for the warnings and ignore them. They are leaks only in the sense that a static variable is a leak, ie not at all. Yeah, the bottom line here is that valgrind will warn about many things that are not genuine problems. You need to learn how to judge the tool's reports. A single allocation that is still reachable at program exit is almost never a real problem. If it's unreachable, or there's a lot of instances, it may be worth worrying about. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE using query; per-row function calling problem
In my -1 example, am I right in assuming that I created a correlated subquery rather than an correlated one? I'm confused about the difference. Correlated: has a where clause that references the outer query Un-correlated: not correlated Because of the where clause a correlated sub-query will return a different record for each row whereas an un-correlated sub-query will return the same record for all rows since the where clause (if any) is constant. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE using query; per-row function calling problem
That's interpretation of subselect is ok, when it contains only stable functions. Maybe add a warning when subselect contains volatile function. 2011/9/2, Rory Campbell-Lange r...@campbell-lange.net: On 02/09/11, Tom Lane (t...@sss.pgh.pa.us) wrote: Rory Campbell-Lange r...@campbell-lange.net writes: I'm doing an UPDATE something like this: UPDATE slots SET a = 'a' ,b = (SELECT uuid_generate_v1()) WHERE c = TRUE; Each updated row in slots is getting the same value for b. That's Postgres' interpretation of an uncorrelated sub-SELECT: there's no reason to do it more than once, so it doesn't. Is there a way of getting a per-row value from uuid_generate_v1() without doing a PL loop? Drop the word SELECT. Why did you put that in in the first place? Hi Tom Good question to which I don't know the answer. Thanks very much for the advice. I was able to force a per-row call to uuid_generate_v1 by using this pattern UPDATE r_slots SET b = (SELECT y.x FROM (select -1 as n, uuid_generate_v1() as x )y WHERE y.n != r_slots.id) ... But b = uuid_generate_v1() is a lot simpler! In my -1 example, am I right in assuming that I created a correlated subquery rather than an correlated one? I'm confused about the difference. Many thanks Rory -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC XA resource bug?
There is a view named pg_prepared_xacts which contains list of
prepared transactions in all databases of current instance.
PGXAConnection uses following query to retrive prepared transactions:
SELECT gid FROM pg_prepared_xacts.
Shouldn't it be the following: SELECT gid FROM pg_prepared_xacts
where owner = current_user in order to retrive prepared transactions
that only belongs to current database (connection is opened to)?
My test case is the following:
I have two database: a and b
Following source code is executing under JBoss AS
DataSource aDS = (DataSource) new InitialContext().lookup(java:jdbc/a-DS);
DataSource bDS = (DataSource) new InitialContext().lookup(java:jdbc/b-DS);
UserTransaction tx = (UserTransaction) new
InitialContext().lookup(java:comp/UserTransaction);
try
{
tx.begin();
Connection aConnection = aDS.getConnection();
aConnection.createStatement().execute(insert into t values (1));
aConnection.close();
Connection kamailioConnection = bDS.getConnection();
bConnection.createStatement().execute(insert into t values (1));
bConnection.close();
tx.commit();
}
catch (Exception e)
{
tx.rollback();
e.printStackTrace();
}
In this example I do the following:
1. Start the global transaction.
2. Insert a row into each database.
3. Tell transaction manager (TM) to commit changes in both databases.
4. TM tells each resource to prepare for commit.
5. Two rows appear in pg_prepared_xacts.
6. Kill JBoss in order to test recovery mechanism.
7. Start JBoss.
After JBoss is started I expect both transactions to be committed. But
during commit of prepared transaction in database b I've got this
error:
Must be superuser or the user that prepared the transaction.
at
org.postgresql.xa.PGXAConnection.commitPrepared(PGXAConnection.java:444)
at org.postgresql.xa.PGXAConnection.commit(PGXAConnection.java:371)
at
org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.commit(XAManagedConnection.java:279)
at
com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord.topLevelCommit(XAResourceRecord.java:442)
at
com.arjuna.ats.arjuna.coordinator.BasicAction.doCommit(BasicAction.java:2789)
at
com.arjuna.ats.arjuna.coordinator.BasicAction.doCommit(BasicAction.java:2705)
at
com.arjuna.ats.arjuna.coordinator.BasicAction.phase2Commit(BasicAction.java:1788)
at
com.arjuna.ats.arjuna.recovery.RecoverAtomicAction.replayPhase2(RecoverAtomicAction.java:72)
at
com.arjuna.ats.internal.arjuna.recovery.AtomicActionRecoveryModule.doRecoverTransaction(AtomicActionRecoveryModule.java:153)
at
com.arjuna.ats.internal.arjuna.recovery.AtomicActionRecoveryModule.processTransactionsStatus(AtomicActionRecoveryModule.java:252)
at
com.arjuna.ats.internal.arjuna.recovery.AtomicActionRecoveryModule.periodicWorkSecondPass(AtomicActionRecoveryModule.java:110)
at
com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:789)
at
com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:371)
As far as I've understood from JBoss source code it gets
PGXAConnection to database a instead of b due to implementation of
PGXAConnection.recover method (all prepared transactions xids
returned during recover method call).
--
Best regards.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE using query; per-row function calling problem
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: That's interpretation of subselect is ok, when it contains only stable functions. Maybe add a warning when subselect contains volatile function. We're not likely to do that, because this sort of notation is actually fairly commonly used to hide the volatility of non-stable functions. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variable column name
-Original Message- From: Bill Moran Sent: Thursday, September 01, 2011 8:19 AM To: Bob Pawley Cc: Postgresql Subject: Re: [GENERAL] Variable column name http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html Section 39.5.4 If you're not familiar with plpgsql at all, you might want to start with this: http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html Thanks for the suggestion. Following is my interpretation of what I have read. I am getting an error -- column 1 does not exist Could someone point to what I am doing wrong? Bob Select 2 into point_array ; Select 1 into column ; Loop Execute 'Update library.compare Set' || quote_ident (column[point_array]) || '= (select st_distance (st_geometryn(public.similar.the_geom, 1), (st_geometryn(public.similar.the_geom, point_array)))/ public.similar.prime from public.similar where public.similar.sight_description = ''H_Line'')' -- || newvalue || 'from public.import_process_transfer' || 'where library.compare.process_id = public.import_process_transfer.process_id'; -- || quote_literal(); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variable column name
On 02/09/2011 18:33, Bob Pawley wrote: -Original Message- From: Bill Moran Sent: Thursday, September 01, 2011 8:19 AM To: Bob Pawley Cc: Postgresql Subject: Re: [GENERAL] Variable column name http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html Section 39.5.4 If you're not familiar with plpgsql at all, you might want to start with this: http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html Thanks for the suggestion. Following is my interpretation of what I have read. I am getting an error -- column 1 does not exist Could someone point to what I am doing wrong? Bob Select 2 into point_array ; Select 1 into column ; Hi Bob, I think it is the double-quotes around the 1; just leave them out to get a literal integer 1: select 1 into column; If I understand correctly, the double-quotes make Postgres look for a column named 1. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variable column name
-Original Message- From: Raymond O'Donnell Sent: Friday, September 02, 2011 10:38 AM To: Bob Pawley Cc: Bill Moran ; Postgresql Subject: Re: [GENERAL] Variable column name On 02/09/2011 18:33, Bob Pawley wrote: -Original Message- From: Bill Moran Sent: Thursday, September 01, 2011 8:19 AM To: Bob Pawley Cc: Postgresql Subject: Re: [GENERAL] Variable column name http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html Section 39.5.4 If you're not familiar with plpgsql at all, you might want to start with this: http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html Thanks for the suggestion. Following is my interpretation of what I have read. I am getting an error -- column 1 does not exist Could someone point to what I am doing wrong? Bob Select 2 into point_array ; Select 1 into column ; Hi Bob, I think it is the double-quotes around the 1; just leave them out to get a literal integer 1: select 1 into column; If I understand correctly, the double-quotes make Postgres look for a column named 1. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie Ray I've named columns 1 through 10 so that it will be easy to determine the next column in the loop. When I use the following it works well. Update library.compare Set 1[2] = (select st_distance (st. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variable column name
In response to Bob Pawley rjpaw...@shaw.ca: I am getting an error -- column 1 does not exist snip Select 1 into column ; Where are you selecting 1 from? This query has no FROM clause, so of course the column doesn't exist. The previous query, SELECT 2 INTO point_array is going to put the integer value 2 into the variable point_array, which I'm guessing is not what you want either. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_lock_status not documented
I'm searching for information on pg_lock_status() function, but there seams to be nothing in the docs. Maybe missing? Any hits, at least what each column outputed is. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_lock_status not documented
=?UTF-8?B?TWFydMOtbiBNYXJxdcOpcw==?= martin.marq...@gmail.com writes: I'm searching for information on pg_lock_status() function, but there seams to be nothing in the docs. Maybe missing? It's not documented because it's an internal function that you shouldn't call directly. Look at the docs for the pg_locks view, which is the public API. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_lock_status not documented
Martín Marqués martin.marq...@gmail.com writes: I'm searching for information on pg_lock_status() function, but there seams to be nothing in the docs. Maybe missing? Any hits, at least what each column outputed is. Have a look at the pg_locks view which wraps this function. \d+ pg_locks MartÃn Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgAdmin3 not working with Gnome3
On Fri, 2011-09-02 at 09:30 +0200, Guillaume Lelarge wrote: I guess the bug can be fixed as: If the bug is in pgAdmin3... Could not reproduce any of those issues on Fedora 15. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Variable column name
-Original Message- From: Bill Moran Sent: Friday, September 02, 2011 10:53 AM To: Bob Pawley Cc: Postgresql Subject: Re: [GENERAL] Variable column name In response to Bob Pawley rjpaw...@shaw.ca: I am getting an error -- column 1 does not exist snip Select 1 into column ; Where are you selecting 1 from? This query has no FROM clause, so of course the column doesn't exist. The previous query, SELECT 2 INTO point_array is going to put the integer value 2 into the variable point_array, which I'm guessing is not what you want either. Well, actually that is what I am attempting. I added the from clause and that seems to be acceptable for the column identification. What I am trying to accomplish is to collect distance information between numerous geometries (in this case 8) at the first spatial location and build an array in column 1, one array point at a time. Then the loop moves to the next location, establishes the geometries and updates the column 2 array with these distances. It seems to work when I hard code the column name and array point, so I was hoping to make it work through a loop using variables for column and array point. Does this make sense?? Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variable column name
On Sep 2, 2011, at 2:31 PM, Bob Pawley wrote: It seems to work when I hard code the column name and array point, so I was hoping to make it work through a loop using variables for column and array point. Does this make sense?? Building queries this way is tedious error prone; that's just the way it is. Put the command into a variable, then raise a notice with that variable, then execute it. That way, when you get a failure, you just copy the failed SQL from the notice into an editor, tweak it until it works, then adjust your code accordingly to produce the corrected query. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_lock_status not documented
I know pg_locks, but I thought maybe pg_lock_status had some extra info. Thanks anyway 2011/9/2 Tom Lane t...@sss.pgh.pa.us: =?UTF-8?B?TWFydMOtbiBNYXJxdcOpcw==?= martin.marq...@gmail.com writes: I'm searching for information on pg_lock_status() function, but there seams to be nothing in the docs. Maybe missing? It's not documented because it's an internal function that you shouldn't call directly. Look at the docs for the pg_locks view, which is the public API. regards, tom lane -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looking for an intro-to-SQL book which is PostgreSQL-friendly
All, I'm looking for an intro-to-SQL book for teaching a class, one aimed at folks who know *nothing* about RDBMSes, which is not based on MySQL or MSAccess. The ones I have on my desk are all based on one or the other, except The Manga Guide to Databases, which I can't use in a serious class. The PostgreSQL books I've seen all make the assumption that the reader already knows what an RDBMS is and a little SQL. The sole exception to this may be Beginning Databases with PostgreSQL from Apress, but that book is somewhat out-of-date (last edition, 2005), and teaches some bad habits around keys. Does anyone have other suggestions? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for an intro-to-SQL book which is PostgreSQL-friendly
On Fri, 2 Sep 2011, Josh Berkus wrote: I'm looking for an intro-to-SQL book for teaching a class, one aimed at folks who know *nothing* about RDBMSes, which is not based on MySQL or MSAccess. The ones I have on my desk are all based on one or the other, except The Manga Guide to Databases, which I can't use in a serious class. Does anyone have other suggestions? Josh, I have two suggestions for your intended audience: Rick F. van der Lans' Introduction to SQL, 4th Ed. (or newer if there is one.) This book is the best introduction to pure SQL out there. It also deals extensively with dates, upon which most business and scientific data depend. Very highly recommended. It was recommended to me for the date sections by Joe Celko. I also proof-read Rick's SQL for SQLite so I am partial to the way he explains the language. Joe Celko's SQL Programming Style. This book focuses mostly on DDL and helps the newcomer to SQL transition from procedural languages, files, and records. His suggestions are based on SQL92 (or 99, I forget which) and are implementation agnostic. Also very highly recommended. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I merge two tables?
On Thu, 2011-09-01 at 13:09 -0400, Jerry LeVan wrote: As time goes by the tables on the various computers get out of sync. Is there an elegant way I can get all of the differences (uniquely) merged into a single table? You can try a query involving NOT EXISTS, combined with dblink: http://www.postgresql.org/docs/current/static/dblink.html Effectively the query would be something like: INSERT INTO registrations SELECT * FROM -- fetch remote version of table dblink(..., SELECT * FROM registrations) AS remote_reg(...) WHERE NOT EXISTS (SELECT 1 FROM registrations local_reg WHERE local_reg.id = remote_reg.id); (disclaimer: I didn't test this query out, it's just for illustrating the idea). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for an intro-to-SQL book which is PostgreSQL-friendly
SQL for Dummies is pretty agnostic. Follow that up with SQL Cookbook from O'Reilly and you have a good one two punch! On 2 September 2011 19:48, Josh Berkus j...@agliodbs.com wrote: All, I'm looking for an intro-to-SQL book for teaching a class, one aimed at folks who know *nothing* about RDBMSes, which is not based on MySQL or MSAccess. The ones I have on my desk are all based on one or the other, except The Manga Guide to Databases, which I can't use in a serious class. The PostgreSQL books I've seen all make the assumption that the reader already knows what an RDBMS is and a little SQL. The sole exception to this may be Beginning Databases with PostgreSQL from Apress, but that book is somewhat out-of-date (last edition, 2005), and teaches some bad habits around keys. Does anyone have other suggestions? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - Bret Why should I fret in microcosmic bonds That chafe the spirit, and the mind repress, When through the clouds gleam beckoning beyonds Where shining vistas mock man's littleness? - H.P. Lovecraft, Phaeton (1918)
[GENERAL] CentOS 6 - www.pgrpms.org - SELinux
I'm setting up a new server for a CMS I have written (er, partially, needs work) that uses PostgreSQL as a backend. All my existing CentOS 5 servers, I use pgrpms for PostgreSQL. I would like to do the same with CentOS 6 but I also want to keep SELinux enabled on this box. Do the RPM's in pgrpms work with SELinux out of the box, or will I need to do additional work after the install and each update? Secondly, why does the pgrpms repository repackage libevent 1.4.13 as compat-libevent14? Binary compatibility with upstream is VERY important to me, it seems to me that if PostgreSQL needs a newer libevent, the newer libevent should have the compat package name so that the vendors libevent can continue to be maintained by the upstream vendor. I'm probably going to have exclude libevent from the PostgreSQL yum repo file, download the src.rpm for libevent 2, and repackage it myself with a proper compat name so that it does not conflict with upstreams packaging. This means more work for me not only because I have to modify a spec file, but it means I have to pay closer attention for patches since I will be maintaining it myself. Please in the future (assuming the rpm packagers are reading this) consider leaving vendor libraries alone and using compat for versions of libraries you require that are different than upstream distro packaging. Obviously for PostgreSQL itself you shouldn't need to do that, but for 3rd party libraries your build links against, if at all possible you should. Thank you./* */ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
