Re[2]: [GENERAL] Problems with Vista and Windows 7
I've notice that in some special cases PG uses folder %APPDATA% for Windows user 'postgres', instead of the PostgreSQL-specific application data folder (f.e., C:\PostgreSQL\8.4\data). It results in some unexpected bugs. Could you please be more specific in describing what problems are you seeing on Win7? Regards, Igor Neyman What files are being created in %APPDATA% for the postgres user? Or what's it looking for there that's causing issues? Craig Ringer Thank you for your answers. Well, the beginning of the story in details is here (http://archives.postgresql.org/pgsql-general/2010-06/msg01135.php). Shortly, I tried to use functions from my own dll, but got an error Could not open relation base\16123\16222: No such file or directory. Later I found out, using 'Process Monitor', that for some reason process 'postgres.exe' looked for this relation not in 'C:\PostgreSQL\8.4\data\base\16123\16222' but in 'C:\Users\postgres\AppData\Roaming\MyApplicationName\base\16123\16222' - certainly, there is no such file. Did you check, that registry key Data Directory is set properly in HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 folder? Usually I don't use standard Windows Installer but take binaries. So there is no such branch in registry. When I tried to make test and install it from Installer I got the same result. :( Data Directory was set to PG-specific application dir. Regards, Marina. -- 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] Problems with Vista and Windows 7
2010/7/8 el dorado do_ra...@mail.ru: Thank you for your answers. Well, the beginning of the story in details is here (http://archives.postgresql.org/pgsql-general/2010-06/msg01135.php). Shortly, I tried to use functions from my own dll, but got an error Could not open relation base\16123\16222: No such file or directory. Later I found out, using 'Process Monitor', that for some reason process 'postgres.exe' looked for this relation not in 'C:\PostgreSQL\8.4\data\base\16123\16222' but in 'C:\Users\postgres\AppData\Roaming\MyApplicationName\base\16123\16222' - certainly, there is no such file. Did you check, that registry key Data Directory is set properly in HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 folder? Usually I don't use standard Windows Installer but take binaries. So there is no such branch in registry. When I tried to make test and install it from Installer I got the same result. :( Data Directory was set to PG-specific application dir. So this is an issue with an extension of your own? Can you post the code? You'll have to excuse us being skeptical about PostgreSQL being fundamentally broken in the way you describe. The Windows installer alone is downloaded tens of thousands of times per week, so I'm pretty sure someone would have reported such an issue before now. And that's ignoring the vast amount of testing that goes into every release, including on 32 and 64 bit versions of XP through 7 and 2003 through 2008R2. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re[2]: [GENERAL] Problems with Vista and Windows 7
Thank you, Andrew, it's very interesting and cognitive article. Though it couldn't resolve my problem now. It looks like recommendations - where to put your application data. But I put my data in some definite location and postgres looks for it in another one - that's the strangeness of situation for me. Regards, Marina. It is a vexed issue with Vista/Windows 7. I found this somewhere on a blog on the web (it may help): Regards Andrew -- 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] \COPY ... CSV with hex escapes
On 8 Jul 2010, at 4:21, Craig Ringer wrote: Yes, that's ancient. It is handled quite happily by \copy in csv mode, except that when csv mode is active, \xnn escapes do not seem to be processed. So I can have *either* \xnn escape processing *or* csv-style input processing. Anyone know of a way to get escape processing in csv mode? And what do those hex-escaped bytes mean? Are they in text strings? AFAIK CSV doesn't contain any information about what encoding was used to create it, so it could be about anything; UTF-8, Win1252, ISO-8859-something, or whatever Sybase was using. I'm just saying, be careful what you're parsing there ;) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c359d9f286212106016419! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CREATE PROCEDURAL LANGUAGE plpythonu on windows, with EDB's 8.4.2 installer causes error
When attempting to restore a dump of a database with a pl/pythonu function, I encountered the following error (recreated here with psql): postgres=# \c lustre psql (8.4.2) WARNING: Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for details. You are now connected to database lustre. lustre=# CREATE PROCEDURAL LANGUAGE plpythonu; ERROR: could not load library C:/Program Files/PostgreSQL/8.4/lib/plpython.dll : The specified module could not be found. This is a throwaway VM that I intend to do a little bit of testing on. I installed python 2.7 for windows, thinking that the module that wasn't found was another dll that plpython.dll was in turn dependent on, but doing so hasn't helped - besides, I always understood that the shipped PLs don't have any external dependencies (the docs indicate that it's a simple matter of createlang plpythonu dbname). It's quite clear that plpython.dll does exist in the directory specified. What should I do? -- Regards, Peter Geoghegan -- 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] CREATE PROCEDURAL LANGUAGE plpythonu on windows, with EDB's 8.4.2 installer causes error
Postgresql 8.4 on windows (stackbuilder install) is compiled against Python 2.6, it is probably looking for the dll python26.dll and can't find it. To get it to work, you need to install python 2.6. Sim On 7/8/2010 1:47 PM, Peter Geoghegan wrote: When attempting to restore a dump of a database with a pl/pythonu function, I encountered the following error (recreated here with psql): postgres=# \c lustre psql (8.4.2) WARNING: Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for details. You are now connected to database lustre. lustre=# CREATE PROCEDURAL LANGUAGE plpythonu; ERROR: could not load library C:/Program Files/PostgreSQL/8.4/lib/plpython.dll : The specified module could not be found. This is a throwaway VM that I intend to do a little bit of testing on. I installed python 2.7 for windows, thinking that the module that wasn't found was another dll that plpython.dll was in turn dependent on, but doing so hasn't helped - besides, I always understood that the shipped PLs don't have any external dependencies (the docs indicate that it's a simple matter of createlang plpythonu dbname). It's quite clear that plpython.dll does exist in the directory specified. What should I do? -- 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] CREATE PROCEDURAL LANGUAGE plpythonu on windows, with EDB's 8.4.2 installer causes error
On Thu, Jul 8, 2010 at 12:30 PM, Sim Zacks s...@compulab.co.il wrote: Postgresql 8.4 on windows (stackbuilder install) is compiled against Python 2.6, it is probably looking for the dll python26.dll and can't find it. To get it to work, you need to install python 2.6. Correct. We use the ActiveState distros of Python, Perl and TCL on Windows (and on the Linux builds from 9.0), but others should work too, provided they're the correct major version. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] CREATE PROCEDURAL LANGUAGE plpythonu on windows, with EDB's 8.4.2 installer causes error
Thanks guys; I've installed Python 2.6.4 from the official MSI installer, and everything works fine. -- Regards, Peter Geoghegan -- 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] optimizer choosing the wrong index
Hello Merlin,
thanks for your help.
can you supply the plans on the actual tables? the 'wrong' index
might actually be the 'right' one if expires_on is of high cardinality
(perhaps it's distributed badly and the table needs a stats tweak to
make it correct).
test=# select count(*) total, count(distinct client_id) ids,
count(distinct expires_on) dates from ps;
total | ids | dates
++
213645 | 123366 | 213549
I played arround with set statistics on both columns, but that didn't
seem to help. (I did run analyze)
You can probably force the right index like this:
explain analyze select * from ps where (client_id, expires_on) =
('foo', '2010-11-24'::timestamp);
That didn't seem to work either:
test=# explain select * from ps where (client_id, expires_on) =
('123', '24.11.2010'::timestamp);
QUERY PLAN
-
Index Scan using idx_wrong on ps (cost=0.00..8.29 rows=1 width=53)
Index Cond: (expires_on = '2010-11-24 00:00:00'::timestamp without time zone)
Filter: ((client_id)::text = '123'::text)
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimizer choosing the wrong index
Martin Below machtin.be...@googlemail.com writes: test=# select count(*) total, count(distinct client_id) ids, count(distinct expires_on) dates from ps; total | ids | dates ++ 213645 | 123366 | 213549 That says the expires_on column is practically unique, which makes me think the planner is indeed making the right choice. 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] Problems with Vista and Windows 7
-Original Message- From: el dorado [mailto:do_ra...@mail.ru] Sent: Thursday, July 08, 2010 3:47 AM To: pgsql-general@postgresql.org Subject: Re[2]: Problems with Vista and Windows 7 I've notice that in some special cases PG uses folder %APPDATA% for Windows user 'postgres', instead of the PostgreSQL-specific application data folder (f.e., C:\PostgreSQL\8.4\data). It results in some unexpected bugs. Could you please be more specific in describing what problems are you seeing on Win7? Regards, Igor Neyman What files are being created in %APPDATA% for the postgres user? Or what's it looking for there that's causing issues? Craig Ringer Thank you for your answers. Well, the beginning of the story in details is here (http://archives.postgresql.org/pgsql-general/2010-06/msg01135 .php). Shortly, I tried to use functions from my own dll, but got an error Could not open relation base\16123\16222: No such file or directory. Later I found out, using 'Process Monitor', that for some reason process 'postgres.exe' looked for this relation not in 'C:\PostgreSQL\8.4\data\base\16123\16222' but in 'C:\Users\postgres\AppData\Roaming\MyApplicationName\base\1612 3\16222' - certainly, there is no such file. Did you check, that registry key Data Directory is set properly in HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 folder? Usually I don't use standard Windows Installer but take binaries. So there is no such branch in registry. When I tried to make test and install it from Installer I got the same result. :( Data Directory was set to PG-specific application dir. Regards, Marina. So, if you just use binaries (no registry entries created), could you try to set PGDATA environment variable, and see if this helps? Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DataArchitect version 4.2 Now Available
Rancho Santa Margarita, CA -- 6 July 2010 -- theKompany.com, producers
and distributors of high-quality open source and commercial Linux
software, are pleased to announce the release of DataArchitect 4.2.
DataArchitect is a powerful tool that provides ERD capability like
PowerDesigner as well as the ability to graphically reverse engineer,
create and modify a database through ODBC and perform advanced SQL
functions. For a single price you get all the supported platforms,
Linux (32, 64 bit and PowerPC), Solaris, Windows (32 and 64 bit) and
Mac OS X. Discover what database professionals have known for years -
that enterprise databases are designed with enterprise modeling tools.
Why has this been a secret for so many? Why do so many labor with
buckets full of error prone, undocumented, SQL? Well it seems no one
has been able to pack the features of a quality data modeling tool
into a product at an accessible price - not until now.
Data Architect is an emerging enterprise tool for designing databases
at all levels of an organization using the same concepts found in
tools costing thousands of dollars!
Major Features:
* Workspace/Project oriented environment
* ERD centric data modeling
* Logical Data Model
* Physical Data Model (Oracle, ODBC, SQL92, MySQL, PostgreSQL, DB2,
FireBird, MS SQL, SQLBase)
* model panner ('birds eye view')
* quick find and navigation from output window
* model validation
* full documentation capabilities
* integrated Advanced SQL Editor with syntax highlighting
* save/load SQL sessions
* integrated ODBC
* multi-platform (Linux,Windows XP, Solaris, HP-UX and OSX)
* open file format (XML)
* DBMS specific features such as data types and SQL syntax
* reverse engineer existing databases
* generate create-scripts
* print model
* print data dictionary
* liberal use of tool-tips to help Users
* integrated Team Diagram (general purpose diagramming)
* supports table inheritance
* supports; table, view, domain, sequence, procedure, and more
DBMS's supported;
* generic using ODBC
* generic using SQL92
* MySQL
* PostgreSQL
* DB2
* InterBase/FireBird
* MS SQL
* Gupta SQLBase
* Oracle
What’s new and updated in this release:
* Full UNICODE support
* Advanced SQL Editor:
threaded queries
sql bindings for manipulating large objects
query cancel : to stop long running queries
transaction support
* Improved workspace and project behaviour
* tksql:
UNICODE support
history for executed statements
transaction support
various fixes
* many fixes for the reported issues
DataArchitect is just $149.95 for the download version and $179.95 for
the boxed version, this includes support for MySQL, Firebird,
PostgreSQL, SQL92, ODBC. SQLBase support is an additional $49.95, DB2
support is an additional $74.95 and Oracle or SQL Server support is an
additional $99.95. All supported platforms are included for the
single price. You can get more information, try a demo or order from
http://www.thekompany.com/products/dataarchitect/
--
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] optimizer choosing the wrong index
On Thu, Jul 8, 2010 at 10:11 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Martin Below machtin.be...@googlemail.com writes:
test=# select count(*) total, count(distinct client_id) ids,
count(distinct expires_on) dates from ps;
total | ids | dates
++
213645 | 123366 | 213549
That says the expires_on column is practically unique, which makes me
think the planner is indeed making the right choice.
I think so too, but suppose we wanted to force the other plan anyways:
select * from ps where (client_id, expires_on) =
('123', '24.11.2010'::timestamp) and (client_id, expires_on) ('123',
null) order by client_id, expires_on;
I'd be curious to see explain analyze (not explain) comparisons for
the 'wrong' index vs above. I suspect the plan is 'correct' for
*most* of the data, or you cherry picked (or unluckily drew) a bad
value to get your 22 times speed difference.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimizer choosing the wrong index
Merlin Moncure mmonc...@gmail.com writes:
I think so too, but suppose we wanted to force the other plan anyways:
select * from ps where (client_id, expires_on) =
('123', '24.11.2010'::timestamp) and (client_id, expires_on) ('123',
null) order by client_id, expires_on;
A simpler way to force use of the other index is
begin;
drop index wrong_index;
explain analyze my_query;
rollback;
I'd be curious to see explain analyze (not explain) comparisons for
the 'wrong' index vs above. I suspect the plan is 'correct' for
*most* of the data, or you cherry picked (or unluckily drew) a bad
value to get your 22 times speed difference.
Yeah, it would be interesting to see explain analyze output for both
cases, and for a few different values of the expires_on date if this
one is unlike the typical value.
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
[GENERAL] weird empty return from select problem; periodically get no data returned - could it be a network issue?
I have a number of Perl programs of similar form to this:
$dbh=DBI-connect(dbi:Pg:dbname=$dbname;host=${dbserver};, $dbuser, $dbpasswd,
{PrintError = 0, PrintWarn = 0, AutoCommit = $autocommit}) or
errexit( Unable to connect to dbname $dbname, err: $DBI::errstr);
errexit(No db handle) unless ($dbh);
#update statement definition here
my $update_info_sth=$dbh-prepare($stmt) or errexit(Cannot prepare handle for
$stmt; , $DBI::errstr);
#stmt=select statement definition here; selects some data ordered by date,
limit n, where n is about 300 or so, depending on the exact program
my $select_info_sth=$dbh-prepare($stmt) or errexit(Cannot prepare handle for
$stmt; , $DBI::errstr);
trace_output(after prepare of select stmt);
$select_info_sth-execute() or errexit(Cannot execute select_info_sth;
,$select_info_sth-errstr);
trace_output(after execute of select stmt);
my (%info, @data);
trace_output(fetching domain info);
while (@data = $select_info_sth-fetchrow_array) {
foreach (@data) { $_='' unless defined}
next if ($data[0] eq '');
$info{$data[0]}=$data[1];
$update_sth-execute($data[0]) or errexit(Cannot update table processing
column for id $data[0]; ,$update_sth-errstr);
trace_output(processing set true for id $data[0], dom: $data[1]);
}
##check for problems with premature termination
errexit(Error in fetching:, $select_info_sth-errstr) if
$select_info_sth-err;
#not really an error, just nothing to process:
if ((scalar keys %info) == 0) {
trace_output(No ids returned);
$dbh-disconnect;
exit 0;
}
The trace_output and errexit subroutines are standard logging-type things.
After the SELECT runs, the program should take the ids returned, and process
each, doing whatever it is supposed to do. The SELECT, in this case, is
ordering data by a date, so that we are processing the oldest data. Therefore,
data should always be returned.
This is a pg cluster installation, using version 8.3.5.
Many instances of these programs run all day long, some on a regular Debian
Lenny server, others through exec hosts in a Sun Grid. Most of the time, data
is returned, and the program proceeds along its way, no problem.
Periodically (I see no pattern to the times), the program will exit with the
No ids returned message in the log. No errors or anything are in the
database log, that I can find. I have seen in the log processes connecting and
running the main SELECT at apparently the appropriate time, then a rollback
(presumably due to the disconnect), and disconnect.
I don't really understand why the query returns nothing periodically, then
works fine again seconds later. The database server is quite busy, doing
thousands of queries all the time.
Any explanations or ideas? The processing works, because other iterations of
the program are constantly running, so the next attempt returns data, and runs
as normal. However, it bugs me that sometimes a query that should work is
returning no results, for no discernable reason.
Thanks,
Susan
Re: [GENERAL] optimizer choosing the wrong index
Heyho! On Wednesday 07 July 2010 13.42:59 Martin Below wrote: I'm facing a strange problem where the optimizer does pick the wrong index. What's not clear to me is why you want pg to chose a particular index. Are you having performance issues? (The case you show is a table with 200k rows - this doesn't look like very much.) cheers -- vbi -- Verbing weirds language. -- Calvin Hobbes signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Problems with Vista and Windows 7
I should add that I have no problems running Postgres in Windows 7. The Windows installer works Ok for me. In this thread I am not clear about whether it is postgres data (data written and read by PostgreSQL) or user data that is being discussed. Regards Andrew On Thu, Jul 8, 2010 at 5:53 PM, Dave Page dp...@pgadmin.org wrote: 2010/7/8 el dorado do_ra...@mail.ru: Thank you for your answers. Well, the beginning of the story in details is here (http://archives.postgresql.org/pgsql-general/2010-06/msg01135.php). Shortly, I tried to use functions from my own dll, but got an error Could not open relation base\16123\16222: No such file or directory. Later I found out, using 'Process Monitor', that for some reason process 'postgres.exe' looked for this relation not in 'C:\PostgreSQL\8.4\data\base\16123\16222' but in 'C:\Users\postgres\AppData\Roaming\MyApplicationName\base\16123\16222' - certainly, there is no such file. Did you check, that registry key Data Directory is set properly in HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 folder? Usually I don't use standard Windows Installer but take binaries. So there is no such branch in registry. When I tried to make test and install it from Installer I got the same result. :( Data Directory was set to PG-specific application dir. So this is an issue with an extension of your own? Can you post the code? You'll have to excuse us being skeptical about PostgreSQL being fundamentally broken in the way you describe. The Windows installer alone is downloaded tens of thousands of times per week, so I'm pretty sure someone would have reported such an issue before now. And that's ignoring the vast amount of testing that goes into every release, including on 32 and 64 bit versions of XP through 7 and 2003 through 2008R2. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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 Postgres problem
We are having a very strange issue where for certain records, queries sent via JDBC return no results when the identical query via command line or pgadmin return non-null (expected) results. Example below. We are running 8.3.9. SELECT client_id FROM locations WHERE external_id = '278'; Doing an EXPLAIN ANALYZE from psql gives: Seq Scan on locations (cost=0.00..17.01 rows=1 width=4) (actual time=0.189..0.197 rows=1 loops=1) Filter: ((external_id)::text = '278'::text) Total runtime: 0.245 ms And doing the same thing from JDBC stmt.executeQuery() gives: Seq Scan on locations (cost=0.00..17.93 rows=1 width=4) (actual time=0.206..0.206 rows=0 loops=1) Filter: ((external_id)::text = '278'::text) Total runtime: 0.221 ms Note the 0 rows returned via JDBC. Has anyone ever heard of anything like this before? Thanks, Tyler Hains IT Director ProfitPoint, Inc. www.profitpointinc.com
[GENERAL] Wrote a proc for massive updates - will I have problems?
Hi gang, I wrote a PL/Tcl stored proc to do paging updates (we have very large tables, and updates are these frustrating things that we stare at, not knowing if they are in a deadlock, or progressing, or when they will finish). So, I wrote a tcl proc that runs like this: SELECT paging_update( 'mdx_core.facility', 'facility_type_code = ''U''', 'facility_id IN (SELECT facility_id FROM temp_up)', 1000 ); Which replaces this: UPDATE 'mdx_core.facility' SET facility_type_code = 'U' WHERE facility_id IN (SELECT facility_id FROM temp_up) This pages through the table, applying the updates one page (1000 rows) at a time. Every page, it uses RAISE INFO to feed back the progress. While purists will argue that I've achieved nothing and that given patience the UPDATWE will do the same thing, the fact that we can see the progress, know that we do not have a locking problem and can estimate when the update is finished is a massive improvement. Better yet, this proc can be used in our SQL scripts and ad-hoc queries. Was there something else I could have done? I would love to hear suggestions. Otherwise, considering that this is a Tcl proc that builds a dynamic query and excutes as an untrusted function, can I expect any problems? My main concern: Will it run within the scope of the current transaction? It uses spi_exec and there are no START TRANSACTION/COMMIT/etc commands in the code. Thanks! Carlo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No PL/PHP ? Any reason?
Obviously we need to improve our documentation. What led you to believe it does not exist? This is my fault entirely. When I Googled for this, I flailed around with fancy terms that didn't connect. And, as you pointed out, its not in the core distibution or the foundry. But I didn't consider the product would be logically called pl/php until I wrote this post! * PHP is not as stable, mature, secure, or well designed as Perl/Tcl/Python. When I couldn't find any reference to pl/php I had assumed this was the leading reason it didn't exist. Nobody uses pl/php. I'm not a PHP developer (but after programmer, but my understanding is that the PHP community is over-represented with HTML designers using PHP to create dynamic content. What I have seen was lots of in-line HTML/PHP programming with no understanding of seperating the presentation from the business logic. But this is not PHP's fault. However, it stands to reason that there ARE people writing good PHP code with a seperation between the business/model and the presentation layer. This code would represent the business process repository and could be shared with other applications (especially non-PHP ones) either via a web service or as a stored proc. Web services are fussy things, whereas if you have a connection to a DB already, a stored proc is a simple thing. Carlo Greg Sabino Mullane g...@turnstep.com wrote in message news:41933015e64e0593a31f4e6cc30ee...@biglumber.com... -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Is there any technical obstacle to anyone creating PL/PHP? I am cruious as to why it doesn't alreay exist. Obviously we need to improve our documentation. What led you to believe it does not exist? As pointed out downthread, it does exist (if not maintained). I mean, I love my Tcl support, and I know this is part of PG's legacy... but Tcl and no PHP? I figure there's a tech reason for this - the demand must be there! No? No, I'd say the demand is most definitely not there. I support a great number of clients, and pretty much everyone uses pl/pgsql, a great many use pl/perl, and a handful use pl/tcl or pl/python or pl/ruby. Nobody uses pl/php. Some major strikes against it (consider these todo items for those who would like to see pl/php live again): * No trusted/untrusted versions * Not in core * Not even in contrib or pgfoundry or github * It seems to suffer from a lot of configuration issues * Hard to find: ** First google hit on pl/php is projects.commandprompt.com/public/plphp ** Which simply says: Go here instead: https://redmine.commandprompt.com/ ** Which stops you with a login and password page * The documentation is a mess (dead URLs, mislabelled sections) * PHP is not as stable, mature, secure, or well designed as Perl/Tcl/Python. Which makes Postgres people less likely to consider it. * They chose backslash '\' as their namespace delimiter. Backslash! Okay, that last one isn't a major strike, but it's damn annoying (and indicative of the poor design of the language :) - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201006220936 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkwgv9MACgkQvJuQZxSWSsgULQCfUB7AtsvETYJAI7okRdCvSh3D d6AAnA+GfxpeUqGrXw0CMhB8mWNH0wSF =xLp+ -END PGP SIGNATURE- -- 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
[GENERAL] getting the last N tuples of a query
Hi! if a want the first 5,10,N tuples of a query (even without order) i just have to do a: select * from table limit 10; but, What can i do to get the last 10 tuples ??? i try to do: select * from table limit -10; :-D but that query return 0 tuples . So, what is the right way to do that with no order -- 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] getting the last N tuples of a query
On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: Hi! if a want the first 5,10,N tuples of a query (even without order) i just have to do a: select * from table limit 10; That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whichever 10 it can, and in practice, the results will change given enough inserts or deletes. So, what is the right way to do that with no order Without an order by clause, there is no concept of first or last. Once you have the order by clause, combine your limit with ascending or descending sorts to get the first or last, respectively. -- 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] GSS Authentication
Hope you don't mind me resurrecting this thread - but I have made a fair bit of headway in my endeavours. So, the big issue I had was a bug in Microsoft's ktpass ( http://support.microsoft.com/kb/919557) that was on the server. After that I spent a bit of time on my windows client fiddling trying to get it to work. I had set PGSRVKRBNAME, tried setting PGGSSAPI however, I wasn't using the FQDN of my database server. When I went from dbhost to dbhost.lab2k.net, I was able to connect. Hopefully this may help someone else in the future. Now my next step is to see if I can make a connection from a Java application with JDBC. Bryan. On Wed, Jun 16, 2010 at 10:17 AM, greigw...@comcast.net wrote: OMG!!! I finally got it working. Problem was that on the windows side on the service account within the account options, we needed to check Use DES encryption types for this account. I had that changed on the AD side and that fixed the whole problem. Bryan, if you're still trying to get this to work I'd be happy to help if I can. Thanks all for the help. Greig - Original Message - From: Greig Wise greigw...@comcast.net To: Bryan Montgomery mo...@english.net Cc: pgsql-general pgsql-general@postgresql.org Sent: Wednesday, June 16, 2010 1:09:16 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication Nope. I get this: kinit(v5): Client not found in Kerberos database while getting initial credentials On Jun 15, 2010, at 10:03 PM, Bryan Montgomery wrote: I'm not in front of a linux machine, but does kinit -kt postgres.keytab -S POSTGRES/host.domain.com grant a ticket without asking for the password? On Tue, Jun 15, 2010 at 2:38 PM, greigw...@comcast.net wrote: As suggested below, I just tried this: kinit -S POSTGRES/host.domain.com user (where user is my account name in AD). That then asked for my password and when I entered it, it seemed to work. And now klist shows that I have a ticket. Doing it this way though, the keytab file doesn't seem to come into play. Does this point to something in my keytab file being wrong? I did this: klist -ket postgres.keytab and got: KVNO Timestamp Principal - 3 12/31/69 19:00:00 POSTGRES/host.domain@domain.comhttp://domain.com/(DES cbc mode with RSA-MD5) That timestamp seems kinda funky, doesn't it? 12/31/69? That can't be right, can it? Thanks again. Greig - Original Message - From: Stephen Frost sfr...@snowman.net To: Bryan Montgomery mo...@english.net Cc: greigw...@comcast.net, pgsql-general@postgresql.org Sent: Saturday, June 12, 2010 8:35:13 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * Bryan Montgomery (mo...@english.net) wrote: I've been trying this as well off and on. In my case I'm not convinced the AD configuration is correct (And someone else manages that). Yeah, that can be a challenge.. but it's *definitely* possible to get it set up and working correctly. Can you use kinit with the key tab options to get a good response from the server? I think I should be able to do this .. $ kinit -V -k -t poe3b.keytab HTTP/poe3b.lab2k.net kinit(v5): Preauthentication failed while getting initial credentials err, I'm not sure that should be expected to work. What does klist -ek keytab file return? Also, you should be able to kinit to *your* princ in the AD, and if you can do that, you should be able to use your princ to request the service princ ticket from the KDC by doing kinit -S HTTP/poe3b.lab2k.net your.princ Also, provided your *client* is set up/configured correctly, you should be able to see that it acquires the ticket (by using klist) when you try to connect to the server, even if the server is misconfigured. I'd be interested to know if you get something different - and the steps you went through on the AD side. You have to create an account in Active Directory for the PG service and then use: ktpass /princ POSTGRES/myserver.mydomain@mydomain.comhttp://mydomain.com//mapuser postg...@mydomain.com /pass mypass /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL /out krb5.keytab Then copy that krb5.keytab to the server. Note that you then have to adjust the server config to have service name set to POSTGRES, and adjust clients using the environment variables to indiciate they should ask for POSTGRES (instead of the postgres default). Thanks, Stephen
Re: [GENERAL] GSS Authentication
Bryan, * Bryan Montgomery (mo...@english.net) wrote: After that I spent a bit of time on my windows client fiddling trying to get it to work. I had set PGSRVKRBNAME, tried setting PGGSSAPI however, I wasn't using the FQDN of my database server. When I went from dbhost to dbhost.lab2k.net, I was able to connect. Do you have reverse DNS working..? That's typically what Kerberos uses to determine the ticket to request from the KDC. Hopefully this may help someone else in the future. Thanks for the follow-up! Now my next step is to see if I can make a connection from a Java application with JDBC. I'd certainly like to hear how this goes.. I don't know if the changes to support GSSAPI were ever merged into the main JDBC driver. If not, perhaps we can encourage them to merge them. There was a version built that I was able to use under Linux to successfully auth using Kerberos (iirc anyway :). Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] getting the last N tuples of a query
Hello. I agree Ben. But,I try your question as an SQL puzzle. Doses this SQL meet what you want? select * from wantlast offset (select count(*)-10 from wantlast); --test case create table wantlast(col1 int); insert into wantlast select g from generate_series(1,1000) as g; postgres=# select * from wantlast offset (select count(*)-10 from wantlast); col1 -- 991 992 993 994 995 996 997 998 999 1000 (10 rows) postgres=# analyze wantlast ; ANALYZE postgres=# explain select * from wantlast offset (select count(*)-10 from wantlast); QUERY PLAN -- Limit (cost=17.91..30.52 rows=900 width=4) InitPlan 1 (returns $0) - Aggregate (cost=16.50..16.52 rows=1 width=0) - Seq Scan on wantlast (cost=0.00..14.00 rows=1000 width=0) - Seq Scan on wantlast (cost=0.00..14.00 rows=1000 width=4) (5 rows) *I try this test Postgresql8.4.4 On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: Hi! if a want the first 5,10,N tuples of a query (even without order) i just have to do a: select * from table limit 10; That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whichever 10 it can, and in practice, the results will change given enough inserts or deletes. So, what is the right way to do that with no order Without an order by clause, there is no concept of first or last. Once you have the order by clause, combine your limit with ascending or descending sorts to get the first or last, respectively. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with table structure
Hi, I am having some uncertainty while designing the following structure: I have two sets of data: * arqueology sites (can be natural): id name description id_category id_period x y * natural sites (can be arqueological also - bear with me -, so there will be duplicate records in the above table and this): id name description altitude x y and i would like to put these two sites in the same data set and maybe add a new table called site types to categorize each record (maybe a relation table to allow many to many): how can i go about doing it? is this solution decent enough: * sites (generic): id_site name description x y * site_natural id id_site altitude * site_arqueology id id_site id_category id_period But i seem to be missing something. How can i have this in a way that its easy to list only arqueology sites for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated. Thanks Pag
Re: [GENERAL] JDBC Postgres problem
On 9/07/2010 5:24 AM, Tyler Hains wrote: We are having a very strange issue where for certain records, queries sent via JDBC return no results when the identical query via command line or pgadmin return non-null (expected) results. Example below. We are running 8.3.9. Can you post a compilable, self-contained Java program and a database dump that demonstrates this problem? You might find that, in the course of trying to simplify your current code down to something you can publish, you discover what the issue is. If you still have the issue with a small, simplified example then please post it so it can be investigated. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] weird empty return from select problem; periodically get no data returned - could it be a network issue?
On 9/07/2010 2:58 AM, Susan Cassidy wrote: This is a pg cluster installation, using version 8.3.5. Pg cluster? There are quite a few different clustering setups for Pg. Do you mean PgCluster from http://pgfoundry.org/projects/pgcluster/ ? If so, which version and how is it set up? Or some other Pg-based cluster using Bucardo, Slony-II, etc? As for your issue: have you excluded the possibility that there is no data to return? Issues sometimes arise where the data you're expecting to retrieve hasn't been committed by another transaction yet, so it's in the database but not yet visible. As you haven't provided your queries or schema it's hard to know what's going on there. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
