Re: [GENERAL] pg_query transaction: auto rollback? begin or start?? commit or end???
> How can I > > even include something *outside* a transaction *inside* it I was referring to conditions outside the database which you detect while the transaction is in progress and which invalidate the semantic integrity of the transaction as a whole. Under such circumstances you would want to issue a rollback even though technically the transaction went through an *could* be committed. That's about the only case where it makes sense to have a keyword separate from "end", namely "rollback". Because you have the choice: either "end" the transaction or "rollback". All other cases just need "end". They will rollback or commit depending on whether there were any unhandled errors. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Full text index without accents
Finally I create a function like:
CREATE OR REPLACE FUNCTION nonsensible (text) RETURNS text AS $$
DECLARE
var1 varchar;
BEGIN
var1=replace($1, 'á', 'a');
var1=replace(var1, 'é', 'e');
var1=replace(var1, 'í', 'i');
var1=replace(var1, 'ó', 'o');
var1=replace(var1, 'ú', 'u');
var1=replace(var1, 'Á', 'A');
var1=replace(var1, 'É', 'E');
var1=replace(var1, 'Í', 'I');
var1=replace(var1, 'Ó', 'O');
var1=replace(var1, 'Ú', 'U');
return var1;
END
$$LANGUAGE plpgsql immutable;
Then, create text indexes, one for sensible queries and other for
unsensible ones:
CREATE INDEX textindex ON document USING
gin(to_tsvector('spanish',text));
CREATE INDEX textindexn ON document USING
gin(to_tsvector('spanish',nonsensible(text)));
And then make a query sensible or unsensible to accents doing:
SELECT id FROM document WHERE to_tsvector('spanish',text) @@
to_tsquery('spanish','word_with_accent');
or:
SELECT id FROM document WHERE to_tsvector('spanish',nonsensible(text))
@@ to_tsquery('spanish',nonsensible('word_with_accent'));
respectively.
I think postgreSQL uses both indexes as necessary. I believe to remember
reading something about it in the documentation.
Thank you very much,
Mario Barcala
> Here is an example
>
> CREATE FUNCTION dropatsymbol(text) RETURNS text
> AS 'select replace($1, ''@'', '' '');'
> LANGUAGE SQL;
>
> arxiv=# select to_tsvector('english',dropatsymbol('[EMAIL PROTECTED]'));
> to_tsvector
> -
> 'oleg':1 'sai.msu.su':2
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] php + postgresql
First, thanks to everyone who responded to my newbie questions
yesterday, all clear now.
I spent most of today struggling with apparently inconsistent behaviour
while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are
both as supplied with CentOS 5.1, a fairly conservative distro).
It seems that some of PHP's PG functions have changed recently, are
there any known issues with them?
1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?
2.
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg
while ($row = pg_fetch_array($query)) {
$content = $row[0]
}
echo $content;
$content was always 'undeclared'.
3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?
4.
Another weird one was that this statement always failed:
$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'
while this one always worked:
SELECT fld_content FROM tbl_page WHERE fld_pid=1
in a three column table:
fld_pid serial PRIMARY KEY,
fld_name varchar(100) NOT NULL,
fld_content text NOT NULL
while everything worked fine from the psql console.
... but this post is getting too unwieldy. I am reading documentation
but am also under some pressure to get basic things up and running. Any
pointers to good documentation covering PHP + PG, or any well known gotchas?
PS If people want to throw MySQL->PostgreSQL gotchas at me I'm happy to
collate and write up.
Thanks again
Mick
--
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] php + postgresql
admin wrote: > First, thanks to everyone who responded to my newbie questions > yesterday, all clear now. > > I spent most of today struggling with apparently inconsistent behaviour > while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are > both as supplied with CentOS 5.1, a fairly conservative distro). > > It seems that some of PHP's PG functions have changed recently, are > there any known issues with them? > > 1. > I ended up using pg_prepare() and pg_execute() as pg_query() alone just > didn't seem to work. But SELECT statements seemed to be cached or > persistent in some way, such that they "lived" beyond the life of the > PHP script. Is there something I need to know about persistent behaviour > in PG that doesn't exist in MySQL? It sounds like you must be using a connection pooler, so your scripts are acquiring connections that've already been used and had statements prepared for them. If you try to prepare a new statement with the same name it'll fail. I understand that this is a common issue with simple connection poolers, but as I don't deal with them myself I don't have any suggestions for you. Others here may, and I'm sure Google can help out too. > 3. > Some examples I found used PHP's pg_num_rows() function to count the > rows in a result, then iterated through them with a "for" loop ... is > this required behaviour (PHP docs don't appear to discuss this)? Required by what? I'm not sure I really understand your question. Do you mean "does PostgreSQL always return a row count that can then be accessed with pg_num_rows()" ? Or: "Must I iterate through a resultset with a loop over pg_num_rows() rather than using some other method to iterate through the resultset" ? > 4. > Another weird one was that this statement always failed: > > $name = "file.php"; > SELECT fld_content FROM tbl_page WHERE fld_name='$name' "failed" how? What did you expect to happen? What happened instead? What was the exact error message? You always need to ask yourself those questions when reporting any sort of problem. Otherwise, the people reading your question will just have to ask them, so you'll get slower and less useful responses (and fewer of them, as many people will just ignore poorly written questions). -- 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
[GENERAL] C function working with input/ouput tables failed !
Dear Postgre co-users,
---
I just submit to this mailing list and I am not a really good English speaker,
so excuse me if I don't respect some of the rules to apply. Just tell me!
I work on Win32 OS with Postgre/Postgis/PgAdmin and I build dlls with DevCpp to
create C functions.
I would like to create a C function which take a square table in entry, put it
in a matrix structure, do some operations (power, sum, etc
) and return the
result in a square table.
First, I just try to put the input table in a matrix data structure before to
return it in an output table. Not really useful at this time!
I take the pgRouting code as example. But I have two problems!
---
First problem:
When I try to get the input table, the first element is forgotten, then, the
whole matrix is wrong! I have no idea of what happens
Example:
Input table Matrix obtained
By reading tuple by tuple
1 0 2 00 1 0 2
0 1 0 00 0 1 0
0 0 1 00 0 0 1
0 0 0 10 0 0 0
If you are interested to read the code:
Look at the function called: compute_puissance_table
At the line containing : DBG(Update of puissance_m);
Second problem:
When I try to give the output table, only the first line is correctly returned.
I have introduced an analyse stage which reveals that the problem comes from
the loss of the information in my matrix data structure when the function is
called for the second time.
My matrix data structure is the following :
typedef struct puissance_matrix
{
int nl;//number of lines
int nc;//number of columns
int** data;//2D integer data
} puissance_m;
I use palloc to allocate space to the element called data.
At the second call of the function, the element funcctx->user_fctx (that I use
to store my matrix data structure) references the good memory block. Moreover,
the nl and nc element references the good memory block too. But, in the block
pointed by nl and nc, the values stored have changed ! I dont understand why.
I try to use malloc or pgport_palloc instead of palloc: it doesnt work !
Example:
At the first call of my function:
1: BLOCK ANALYSE
1: pointer value of pm [11784980]
1: pointer value of pm->nc [11785068] stores (4)
1: pointer value of pm->nl [11785064] stores (4)
1: pointer value of pm->data [] and stored value () ::
[19517544] (0) ::
[19517548] (1) ::
[19517552] (0) ::
[19517556] (2) ::
[19517568] (0) ::
[19517572] (0) ::
[19517576] (1) ::
[19517580] (0) ::
etc
At the second call of my function:
1: pointer value of pm [11784980]
1: pointer value of pm->nc [11785068] stores (0)
1: pointer value of pm->nl [11785064] stores (0)
If you are interested to read the code:
Look at the function called: puissance_table
At the line containing : DBG("PROBLEMATIK at step %i\n" , call_cntr);
Only for interested people, here is the code of the dllmain.c file.
Notice that there is a foreword due to the resolution of the conflicts generated
by the BUILDING_DLL macro on Win32 Platform (XP).
Just pass this step if youre not interested! And directly go to the line
containing **Power Table**
Thanks for your reading! I hope, a day, Ill help you in response!
CODE OF DLLMAIN.C
/**/
/* TEST DLL TO BUILD THE EXPONENTIATION OF A TABLE */
/* by FBM :: July 2008 */
/**/
/**/
/*RESOLUTION OF THE MACRO CONFLICT FOR BUILDINGDLL*/
/*Adaptation needed for the platform Win32 OS**/
/**/
#if defined(_MSC_VER) || defined(__MINGW32__)
#ifndef _USE_32BIT_TIME_T
#define _USE_32BIT_TIME_T
#endif
#endif
/* BUILDING_DLL ::*/
#ifdef BUILDING_DLL
#error Do not define BUILDING_DLL when building extension libraries
#endif
// Ensure that Pg_module_function are declared __declspec(dllexport)
#ifndef BUILDING_MODULE
#define BUILDING_MODULE
#endif
//Includes (more than needed)
#include "postgres.h"
#include
#include "fmgr.h"
#include "utils/geo_decls.h"
#include "executor/executor.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "utils/array.h"
#include "utils/lsyscache.h"
#include "miscadmin.h"
#include "commands/trigger.h"
#include
#include
#include
#include
#include
/* PG MACROS :: */
#undef PG_MODULE_MAGIC
#undef PG_FUNCTION_INFO_V1
//User's PGMODULEEXPORT MACRO
#if defined(_MSC_VER) || defined(__MINGW32__)
#if defined(BUILDING_MODULE)
#define PGMODULEEXPORT __declspec (dllexport)
#else
#define PGMODULEEXPORT __declspec (dllimport)
#endif
#else
#def
Re: [GENERAL] php + postgresql
On 24/07/2008 10:41, admin wrote:
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?
That's not something I've ever encountered, and I've done a good bit of
PHP+PG at this stage. Can you show us an example? Also, how are you
connecting? - are you simply doing pg_connect() to connect directly,
or is there anything else in the middle - maybe a connection pooler of
some kind?
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg
while ($row = pg_fetch_array($query)) {
$content = $row[0]
}
echo $content;
$content was always 'undeclared'.
Again, this ought to be fine as you've shown itcan you show us the
SELECT statement and other information?
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?
No real need - I generally use the idiom you have above -
$rs = pg_query($sql_string);
while ($row = pg_fetch_assoc($rs)
{
$value = $row['col1'];
// etc
}
Another weird one was that this statement always failed:
$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'
That's because you need to use double-inverted-commas for string
interpolation:
...WHERE fld_name = "$name"
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--
--
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] php + postgresql
admin wrote:
First, thanks to everyone who responded to my newbie questions
yesterday, all clear now.
I spent most of today struggling with apparently inconsistent behaviour
while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are
both as supplied with CentOS 5.1, a fairly conservative distro).
It seems that some of PHP's PG functions have changed recently, are
there any known issues with them?
PHP's functions change on a regular basis I'm afraid. There's a
changelog to track the detail, but the docs give details of larger
changes. You might find it simplest to refer to the docs that come with
your distro.
1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?
You're probably using persistent connections. Don't - they're not much
use with a standard Apache+PHP installation. Prepared queries last for
the length of a session (connection).
2.
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg
while ($row = pg_fetch_array($query)) {
$content = $row[0]
}
echo $content;
$content was always 'undeclared'.
Nothing leaping out at me, but don't refer to columns by index, refer to
them by name.
3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?
Not required. The while($row=) works if you want all rows. Of course if
you just want a page of 20 or so then you might want a for loop.
4.
Another weird one was that this statement always failed:
$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'
while this one always worked:
SELECT fld_content FROM tbl_page WHERE fld_pid=1
1. Don't interpolate variables directly into SQL. Use the parameterised
query functions.
2. Check the error message to see why there's a problem.
... but this post is getting too unwieldy. I am reading documentation
but am also under some pressure to get basic things up and running. Any
pointers to good documentation covering PHP + PG, or any well known
gotchas?
None (other than the fact that persistent connections don't work how a
newbie might want).
PS If people want to throw MySQL->PostgreSQL gotchas at me I'm happy to
collate and write up.
Traditionally MySQL is very "relaxed" about data validity. PostgreSQL
isn't and dates of 00-00- aren't allowed. There are pages of "mysql
gotchas" and "postgresql gotchas" too - google for them.
--
Richard Huxton
Archonet Ltd
--
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] php + postgresql
Hi Mick,
1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone
just didn't seem to work. But SELECT statements seemed to be cached
or persistent in some way, such that they "lived" beyond the life of
the PHP script. Is there something I need to know about persistent
behaviour in PG that doesn't exist in MySQL?
Do you have an example? and what makes you say they are persisting?
2.
Another problem was that no matter how many times I checked and re-
checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg
while ($row = pg_fetch_array($query)) {
$content = $row[0]
}
echo $content;
$content was always 'undeclared'.
are you sure pg_fetch_array($query) is returning any rows? (try echo
$row[0]; within the while loop)
3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ...
is this required behaviour (PHP docs don't appear to discuss this)?
I often do something along the lines of this:
if($stat = pg_exec($dbh, $sql))
{
if($rows = pg_numrows($stat))
{
for($i=0; $i < $rows; $i++)
{
$data = pg_fetch_array($stat, $i);
# do something with $data
}
}
else{echo "no rows returned";}
}
else{echo "query failed";}
4.
Another weird one was that this statement always failed:
$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'
is $name being interpolated correctly when you use it maybe use:
$sql = "SELECT fld_content FROM tbl_page WHERE fld_name='".$name."'";
(or use a prepared statement)
while this one always worked:
SELECT fld_content FROM tbl_page WHERE fld_pid=1
in a three column table:
fld_pid serial PRIMARY KEY,
fld_name varchar(100) NOT NULL,
fld_content text NOT NULL
while everything worked fine from the psql console.
... but this post is getting too unwieldy. I am reading
documentation but am also under some pressure to get basic things up
and running. Any pointers to good documentation covering PHP + PG,
or any well known gotchas?
PS If people want to throw MySQL->PostgreSQL gotchas at me I'm happy
to collate and write up.
Thanks again
Mick
--
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] php + postgresql
On 24/07/2008 11:13, Raymond O'Donnell wrote: $rs = pg_query($sql_string); while ($row = pg_fetch_assoc($rs) Whoops! - while ($row = pg_fetch_assoc($rs)) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] High activity short table and locks
Guillaume Bog wrote: On Wed, Jul 23, 2008 at 11:17 PM, Richard Huxton <[EMAIL PROTECTED]> wrote: I tried a vacuum full and had to stop it as it was blocking the server for too long. Below is the partial results I got. It seems you are right: enormous amount of dead space and rows. I did the same vacuum later and it seems to have improved a lot the performance. I need to check again tomorrow. We don't have autovacuum, but as it seems autovacuum cannot target a specific table, I may prefer do it by cron every minute, as you suggest. There's a pg_autovacuum system table that lets you tune things table-by-table. See the manual for details. In your case, a manual vacuum once a minute will be a huge step forward anyway. vf_cn2fr=# VACUUM FULL VERBOSE lockers ; INFO: vacuuming "public.lockers" INFO: "lockers": found 4228421 removable, 107 nonremovable row versions in 64803 pages Well, that table is at least 1000 times larger than it needs to be. If you've run a VACUUM FULL, you'll want to run REINDEX on all the indexes on that table too. 64803 pages containing 512643700 free bytes are potential move destinations. Ouch! that's a 500MB table holding 100 live rows. You could fiddle around setting up ramdisks and pointing tablespaces there, but I'm not sure it's worth it. If it is possible to have no WAL at all on this table, I'd prefer to try it. It seems completely useless and is probably taking a fair amount of i/o. It's a bit early to be sure if the solution is there, but I feel you already did throw some good light on my dark path, I have to thank you for that. Afraid not. The synchronous_commit setting can reduce the disk I/O though. -- Richard Huxton Archonet Ltd -- 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] php + postgresql
On Thursday 24. July 2008, admin wrote:
>It seems that some of PHP's PG functions have changed recently, are
>there any known issues with them?
I've been using PHP with PostgreSQL for 5 years, and haven't noticed any
substantial changes.
>while ($row = pg_fetch_array($query)) {
> $content = $row[0]
>}
>
>echo $content;
>
>$content was always 'undeclared'.
You have to use an intermediate variable like a handle. Try this:
$handle = pg_query("SELECT whatever FROM foo");
while ($row = pg_fetch_array($handle) {
$content = $row[0];
}
> Any pointers to good documentation covering PHP + PG, or any well
> known gotchas?
You can download my pg+php genealogy app "exodus" from here:
http://solumslekt.org/forays/exodus.tgz
The code is certainly not stellar, but it works for me. Note that the
code is intended for running in a private environment, and there are no
security features whatsoever.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
My Jazz Jukebox: http://www.last.fm/user/leifbk/
--
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] php + postgresql
On Thu, 24 Jul 2008 19:11:36 +0930
admin <[EMAIL PROTECTED]> wrote:
> 2.
> Another problem was that no matter how many times I checked and
> re-checked code, or which pg_fetch_* function I used, copying an
> array member and trying to use it later just would not work, eg
>
> while ($row = pg_fetch_array($query)) {
>$content = $row[0]
> }
>
> echo $content;
>
> $content was always 'undeclared'.
Did the result contain at least 1 row?
Also prefer column names. If you change the schema, order etc...
you'll have less chances to break code.
What do you mean by 'undeclared'?
if(!isset($content)) ?
or just
echo $content doesn't return output?
what about
$content .= $row[0]." # ";
for quick debugging?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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] php + postgresql
On Thu, 24 Jul 2008 12:30:22 +0200
"Leif B. Kristensen" <[EMAIL PROTECTED]> wrote:
> On Thursday 24. July 2008, admin wrote:
> >while ($row = pg_fetch_array($query)) {
> > $content = $row[0]
> >}
> >
> >echo $content;
> >
> >$content was always 'undeclared'.
>
> You have to use an intermediate variable like a handle. Try this:
>
> $handle = pg_query("SELECT whatever FROM foo");
> while ($row = pg_fetch_array($handle) {
> $content = $row[0];
> }
[EMAIL PROTECTED]:~$ php -a
Interactive mode enabled
http://www.webthatworks.it
--
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] C function working with input/ouput tables failed !
FIRST PROBLEM IS SOLVED !
> First problem:
> When I try to get the input table, the first element is forgotten, then, the
> whole matrix is wrong! I have no idea of what happens
>
> Example:
> Input table Matrix obtained
> By reading tuple by tuple
>
> 1 0 2 00 1 0 2
> 0 1 0 00 0 1 0
> 0 0 1 00 0 0 1
> 0 0 0 10 0 0 0
>
> If you are interested to read the code:
> Look at the function called: compute_puissance_table
> At the line containing : DBG(Update of puissance_m);
>
I change :
pm->data[i][j]= DatumGetInt32(SPI_getbinval(tuple,tupdesc, j, &isnull));
To :
pm->data[i][j]= DatumGetInt32(SPI_getbinval(tuple,tupdesc, j+1, &isnull));
In :
>for (i = 0; i < nl; i++)
> {
>DBG("Tuple number %i\n", i+1);
>HeapTuple tuple = tuptable->vals[i];
>for (j = 0; j < nc; j++)
>{
>pm->data[i][j]= DatumGetInt32(SPI_getbinval(tuple,
> tupdesc, j, &isnull));
>}
> }
In fact "SPI_getbinval(tuple,tupdesc, 0, &isnull));" returns NULL;
HOWEVER THE SECOND PROBLEM IS STILL OPEN !
Thanks for your reading!
--
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] Equality search on timestamp value returns no rows
On 2008-07-23 21:24, Keaton Adams wrote: > We run into a problem when an equality search on a timestamp column > returns no records, even when there are records to return, as in this > example: > > mxl=# select * from mxl_scheduler_queue where status_modified = > '2008-07-03 16:55:06.44695-06'; Never use equality with floating point number as they are stored inexact. Use for example: select * from mxl_scheduler_queue where status_modified>='2008-07-03 16:55:06' and status_modified<'2008-07-03 16:55:07'; You can also compile Postgres with integer timestamps which does not have this problem. You'll need to backup/initdb/restore though. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] mac install question
On Jul 24, 2008, at 1:51 AM, Tom Lane wrote: Relative paths sound like the best solution to me, assuming they work. Relative paths work fine. I use this all the time for distributing psql. It looks something like this: install_name_tool -change /path/to/postgresql/lib/libpq.dylib @executable_path/../lib/libpq.dylib psql John DeSoi, Ph.D. -- 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] php + postgresql
Thanks again for replies. I know those questions were pretty vague. I need to set up some methodical test scripts that replicate my problems, so that it is clear what is going on. There does seem to be some evidence of problems historically with PHP and persistent connections in PostgreSQL, on the PHP forums. The advice is typically to avoid them. Mick -- 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] mac install question
Shane Ambler wrote: [EMAIL PROTECTED] wrote: Is the otool program on your system anywhere? Afaik, it should be on any Mac, but maybe it's part of xcode (i hope not- can anyone confirm?) I have a few installs here. now that I think about it - it may be a dev tools only thing. otool is included inside /Developer/usr/bin my working 10.4 that the dev tools is installed on also includes otool at /usr/bin This has been running for a few years so otool at /usr/bin may have come from anywhere. A minimal 10.3 install has otool at /usr/bin I have two 10.3 systems here the one that has otool is an emergency boot setup by TechTool Pro. A normal 10.3 install does not have otool. A clean 10.5 install does not have otool -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] contrib catalogs
Really? I didn't know that ... guess I never tried. I'll have to do some experimenting! Thanx Tom. -- Kevin Tom Lane wrote: Kevin Neufeld <[EMAIL PROTECTED]> writes: This might seem like a silly question, but what are the implications of PostgreSQL allowing developers to create custom catalogs? For example, PostgreSQL currently uses the pg_catalog schema to store system catalogs / relations / functions / etc. Has thought gone into extending the scope to allow contrib modules to create their own catalogs (ie. a PostGIS catalog, cube, tsearch2, etc.)? A superuser can create whatever he wants in pg_catalog. Whether this is a good idea or will behave smoothly is a topic that has not been thought about, to my knowledge. 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] php + postgresql
Mick,
As I haven't seen anyone else say it, I just wanted to throw this in.
I'm not a PHP programmer, so I'm not very sure of PHP's scoping rules,
but this looks to me like a variable scoping problem. If the first
time you've used $content is inside of the while(), it's probably
going out of scope before your echo. Try this:
# Initialize $content before going into the loop.
# This declares it outside the scope of the while()
$content=''';
# Now do your loop
while ($row = pg_fetch_array($query)) {
$content = $row[0]
}
echo $content;
Your loop is a little weird, too. You're not accumulating anything,
you're just saving the previous value. When you exit the loop,
$content will only contain the value from the final row. If that's
your intent, you may save some time by reverse-ordering your query and
using "limit 1". That way you can remove the loop altogether and save
lots of processing time.
--
David Spadea
On Thu, Jul 24, 2008 at 5:41 AM, admin <[EMAIL PROTECTED]> wrote:
> First, thanks to everyone who responded to my newbie questions yesterday,
> all clear now.
>
> I spent most of today struggling with apparently inconsistent behaviour
> while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are both
> as supplied with CentOS 5.1, a fairly conservative distro).
>
> It seems that some of PHP's PG functions have changed recently, are there
> any known issues with them?
>
> 1.
> I ended up using pg_prepare() and pg_execute() as pg_query() alone just
> didn't seem to work. But SELECT statements seemed to be cached or persistent
> in some way, such that they "lived" beyond the life of the PHP script. Is
> there something I need to know about persistent behaviour in PG that doesn't
> exist in MySQL?
>
>
> 2.
> Another problem was that no matter how many times I checked and re-checked
> code, or which pg_fetch_* function I used, copying an array member and
> trying to use it later just would not work, eg
>
> while ($row = pg_fetch_array($query)) {
> $content = $row[0]
> }
>
> echo $content;
>
> $content was always 'undeclared'.
>
> 3.
> Some examples I found used PHP's pg_num_rows() function to count the rows in
> a result, then iterated through them with a "for" loop ... is this required
> behaviour (PHP docs don't appear to discuss this)?
>
> 4.
> Another weird one was that this statement always failed:
>
> $name = "file.php";
> SELECT fld_content FROM tbl_page WHERE fld_name='$name'
>
> while this one always worked:
>
> SELECT fld_content FROM tbl_page WHERE fld_pid=1
>
> in a three column table:
>
> fld_pid serial PRIMARY KEY,
> fld_name varchar(100) NOT NULL,
> fld_content text NOT NULL
>
> while everything worked fine from the psql console.
>
>
> ... but this post is getting too unwieldy. I am reading documentation but am
> also under some pressure to get basic things up and running. Any pointers to
> good documentation covering PHP + PG, or any well known gotchas?
>
> PS If people want to throw MySQL->PostgreSQL gotchas at me I'm happy to
> collate and write up.
>
> Thanks again
> Mick
>
> --
> 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] C function :: SECOND PROBLEM SOLVED :: SPI_finish releases memory!
The second problem is now solved !
> Second problem:
> When I try to give the output table, only the first line is correctly
> returned.
> I have introduced an analyse stage which reveals that the problem comes from
> the loss of the information in my matrix data structure when the function is
> called for the second time.
>
> My matrix data structure is the following :
>
> typedef struct puissance_matrix
> {
>int nl;//number of lines
>int nc;//number of columns
>int** data;//2D integer data
> } puissance_m;
>
> I use palloc to allocate space to the element called data.
>
> At the second call of the function, the element funcctx->user_fctx (that I
> use
> to store my matrix data structure) references the good memory block.
> Moreover,
> the nl and nc element references the good memory block too. But, in the block
> pointed by nl and nc, the values stored have changed ! I dont understand
> why.
> I try to use malloc or pgport_palloc instead of palloc: it doesnt work !
>
> Example:
>
> At the first call of my function:
>
> 1: BLOCK ANALYSE
> 1: pointer value of pm [11784980]
> 1: pointer value of pm->nc [11785068] stores (4)
> 1: pointer value of pm->nl [11785064] stores (4)
> 1: pointer value of pm->data [] and stored value () ::
> [19517544] (0) ::
> [19517548] (1) ::
> [19517552] (0) ::
> [19517556] (2) ::
> [19517568] (0) ::
> [19517572] (0) ::
> [19517576] (1) ::
> [19517580] (0) ::
> etc
>
> At the second call of my function:
>
> 1: pointer value of pm [11784980]
> 1: pointer value of pm->nc [11785068] stores (0)
> 1: pointer value of pm->nl [11785064] stores (0)
>
> If you are interested to read the code:
> Look at the function called: puissance_table
> At the line containing : DBG("PROBLEMATIK at step %i\n" , call_cntr);
>
In fact, SPI_finish releases memory allocated by palloc !!
Then, by using SPI_palloc insted of palloc I solved my second problem !
I think it would be interesting to add this in the documentation of the SPI !
Thnaks for your reading.
--
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] contrib catalogs
Tom Lane wrote:
A superuser can create whatever he wants in pg_catalog. Whether this
is a good idea or will behave smoothly is a topic that has not been
thought about, to my knowledge.
regards, tom lane
Sorry, Tom. I think you are mistaken. In my 8.3 instance, system
catalog modifications are not allowed.
/opt/pgsql83/bin/psql -p 5432 -U postgres postgres
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
---+---+-+---+-+---
postgres | yes | yes | yes | no limit| {}
postgres=# CREATE TABLE pg_catalog.test (id integer);
ERROR: permission denied to create "pg_catalog.test"
DETAIL: System catalog modifications are currently disallowed.
It would be interesting to test a contrib module install in pg_catalog
though.
Cheers,
-- Kevin
--
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] php + postgresql
On Thu, 24 Jul 2008 11:13:52 -0400
"David Spadea" <[EMAIL PROTECTED]> wrote:
> Mick,
>
> As I haven't seen anyone else say it, I just wanted to throw this
> in.
>
> I'm not a PHP programmer, so I'm not very sure of PHP's scoping
> rules, but this looks to me like a variable scoping problem. If
> the first time you've used $content is inside of the while(), it's
> probably going out of scope before your echo. Try this:
[EMAIL PROTECTED]:~$ php -a
Interactive mode enabled
# Initialize $content before going into the loop.
> # This declares it outside the scope of the while()
>
> $content=''';
mistype
> # Now do your loop
>
> while ($row = pg_fetch_array($query)) {
> $content = $row[0]
> }
>
> echo $content;
>
>
>
> Your loop is a little weird, too. You're not accumulating anything,
> you're just saving the previous value. When you exit the loop,
> $content will only contain the value from the final row. If that's
for debugging I suggested:
$content .= $row[0]." # ";
So he could see if any row even if all $row[0] contained '' or null.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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] contrib catalogs
Kevin Neufeld <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> A superuser can create whatever he wants in pg_catalog. Whether this >>> is a good idea or will behave smoothly is a topic that has not been >>> thought about, to my knowledge. > Sorry, Tom. I think you are mistaken. In my 8.3 instance, system > catalog modifications are not allowed. Hm, I had forgotten that the allow_system_table_mods filter extends to anything at all in pg_catalog. But you could easily enable that setting in a test database and then see what happens. 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] contrib catalogs
Thanx for tip, Tom. I'll definitely give that a try in my spare time. Cheers, Kevin Tom Lane wrote: Kevin Neufeld <[EMAIL PROTECTED]> writes: Tom Lane wrote: A superuser can create whatever he wants in pg_catalog. Whether this is a good idea or will behave smoothly is a topic that has not been thought about, to my knowledge. Sorry, Tom. I think you are mistaken. In my 8.3 instance, system catalog modifications are not allowed. Hm, I had forgotten that the allow_system_table_mods filter extends to anything at all in pg_catalog. But you could easily enable that setting in a test database and then see what happens. 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] cursor
I understand that a cursor can be opened and used through sql commands. However, when a host application accesses a particular row on a particular table can that be detected within the sql medium? Or does that information have the be accessed by transmitting from the host? Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] open_sync fails
Basic system setup: Linux 2.4 kernel (heavily modified) Dual core Athlon Opteron 4GB ECC RAM SW RAID 10 configuration with 8 750 Gb disks (using only 500Gb of each disk) connected via LSISAS1068 based card While working on tuning my database, I was experimenting with changing the wal_sync_method to try to find the optimal value. The really odd thing is when I switch to open_sync (O_SYNC), Postgres immediately fails and gives me an error message of: 2008-07-22 11:22:37 UTC 19411 akamai [local] PANIC: could not write to log file 101, segment 40 at offset 1255 8336, length 2097152: No space left on device Even running the test_fsync tool on this system gives me an error message indicating O_SYNC isn't supported, and it promptly bails. So I'm wondering what the heck is going on. I've found a bunch of posts that indicate O_SYNC may provide some extra throughput, but nothing indicating that O_SYNC doesn't work. Can anybody provide me any pointers on this? Thanks --Rick smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] open_sync fails
Rick Weber <[EMAIL PROTECTED]> writes: > Basic system setup: > Linux 2.4 kernel (heavily modified) "Heavily modified" meaning what exactly? Given that no one else has reported such a thing, and the obvious bogosity of the errno code, I'd certainly first cast suspicion on the kernel. 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] open_sync fails
Rick Weber wrote: > While working on tuning my database, I was experimenting with changing > the wal_sync_method to try to find the optimal value. The really odd > thing is when I switch to open_sync (O_SYNC), Postgres immediately fails > and gives me an error message of: > > 2008-07-22 11:22:37 UTC 19411 akamai [local] PANIC: could not write to > log file 101, segment 40 at offset 12558336, length 2097152: No space left on > device Sounds like a kernel bug to me, particularly because the segment is most likely already 16 MB in length; we're only rewriting the contents, not enlarging it. Perhaps the kernel wanted to report a problem and chose the wrong errno. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] open_sync fails
Definitely believable. It gives me an internal avenue to chase down. Thanks --Rick Alvaro Herrera wrote: Rick Weber wrote: While working on tuning my database, I was experimenting with changing the wal_sync_method to try to find the optimal value. The really odd thing is when I switch to open_sync (O_SYNC), Postgres immediately fails and gives me an error message of: 2008-07-22 11:22:37 UTC 19411 akamai [local] PANIC: could not write to log file 101, segment 40 at offset 12558336, length 2097152: No space left on device Sounds like a kernel bug to me, particularly because the segment is most likely already 16 MB in length; we're only rewriting the contents, not enlarging it. Perhaps the kernel wanted to report a problem and chose the wrong errno. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
On Thu, Jul 24, 2008 at 6:33 AM, admin <[EMAIL PROTECTED]> wrote: > Thanks again for replies. > I know those questions were pretty vague. > I need to set up some methodical test scripts that replicate my problems, so > that it is clear what is going on. > > There does seem to be some evidence of problems historically with PHP and > persistent connections in PostgreSQL, on the PHP forums. The advice is > typically to avoid them. php and persistant connections are a foot gun for any database really. There are very strict provisioning rules you have to follow to use them correctly, and they are often NOT the best answer for a given problem. Until they are. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] invalid byte sequence for encoding "UNICODE"
Hi there. Many times, I'm confronting with that strange problem: invalid byte sequence for encoding "UNICODE". So, I guess, Postgresql can't allow me to use some symbols which is not a part of UNICODE. But what is that symbals? I'm attaching a screenshot with THAT dead-symbol. As you can see - it's an unknown symbol in the end of Cyrillic. First of all, I have checked my data with iconv (iconv -f UTF-8 -t UTF-8 data.txt) and there are no errors, so, I guess, there are no dead-symbols. So the question is: is it possible to find a *table* with forbitten characters for encoding "UNICODE"? If I can get it -> I can kill that dead-characters in my program ;-) 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
Re: [GENERAL] invalid byte sequence for encoding "UNICODE"
AlannY <[EMAIL PROTECTED]> writes: > Many times, I'm confronting with that strange problem: invalid byte > sequence for encoding "UNICODE". So, I guess, Postgresql can't allow me > to use some symbols which is not a part of UNICODE. But what is that > symbals? Doesn't it tell you? AFAICS every PG version that uses that error message phrasing gives you the exact byte sequence it's complaining about. It would also be worth asking what PG version you are using anyway. If it's not a pretty recent update then updating might help --- I think there were some bugs in the encoding verification stuff awhile back. 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 runs slow in a transaction
Viktor Rosenfeld <[EMAIL PROTECTED]> writes: > Postgres is indeed selecting a bad plan. Turns out that the index I > created to speed up the UPDATE isn't used inside a transaction block. I was having a hard time believing that, but just noticed that there is a case in which it could be expected to happen, in 8.3. The HOT patch has to prevent use of an index in its creating transaction in some cases. To quote from README.HOT: : Practically, we prevent old transactions from using the new index by : setting pg_index.indcheckxmin to TRUE. Queries are allowed to use such an : index only after pg_index.xmin is below their TransactionXmin horizon, : thereby ensuring that any incompatible rows in HOT chains are dead to them. : (pg_index.xmin will be the XID of the CREATE INDEX transaction. The reason : for using xmin rather than a normal column is that the regular vacuum : freezing mechanism will take care of converting xmin to FrozenTransactionId : before it can wrap around.) : : This means in particular that the transaction creating the index will be : unable to use the index. We alleviate that problem somewhat by not setting : indcheckxmin unless the table actually contains HOT chains with : RECENTLY_DEAD members. (In 8.4 we may be able to improve the situation, : at least for non-serializable transactions, because we expect to be able to : advance TransactionXmin intratransaction.) That "alleviation" could mask the behavior in simple test cases, if you're testing in an otherwise-idle database. But in a real workload it wouldn't be surprising that a new index would fail to be used immediately, if it were built on a table that had been recently UPDATEd. I think also that I tried to duplicate the problem in HEAD rather than 8.3, which means that the TransactionXmin advance code also helped to keep me from seeing it. 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] mac install question
On Thu, Jul 24, 2008 at 12:55 PM, John DeSoi <[EMAIL PROTECTED]> wrote: > > On Jul 24, 2008, at 1:51 AM, Tom Lane wrote: > >> Relative paths sound like the best solution to me, assuming they work. > > Relative paths work fine. I use this all the time for distributing psql. It > looks something like this: > > install_name_tool -change /path/to/postgresql/lib/libpq.dylib > @executable_path/../lib/libpq.dylib psql Yeah, we do that in pgAdmin. In the installer though we have far more executables and libraries in various locations, and scripting the required changes (without hardcoding for specific filenames which is a recipe for future bugs) will be much more tricky. The other downside is that I won't be able to set the library install name correctly of course. I'll have to change that to just the filename. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [PERL DBI] Insertiing data across multiple tables
Does anybody know how to insert data over multiple tables transactionally? The relationship is 1:1 with the latter table having a foreign key constraint. In order to add data to Table2 I need to know the primary key value of the same record in Table1 before it's committed, which in this case is a serial integer which of course is regulated by an implicit index. Does anybody know how to access a column's index in the Perl DBI? -thanks
Re: [GENERAL] mac install question
On Thu, Jul 24, 2008 at 3:57 PM, Shane Ambler <[EMAIL PROTECTED]> wrote: > Shane Ambler wrote: >> >> [EMAIL PROTECTED] wrote: >> >>> Is the otool program on your system anywhere? Afaik, it should be on >>> any Mac, but maybe it's part of xcode (i hope not- can anyone >>> confirm?) >> >> I have a few installs here. > > now that I think about it - it may be a dev tools only thing. Yeah, was afraid of that. Thanks for checking. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERL DBI] Insertiing data across multiple tables
[EMAIL PROTECTED] wrote on 07/24/2008 01:36:27 PM:
> Does anybody know how to insert data over multiple tables
> transactionally? The relationship is 1:1 with the latter table
> having a foreign key constraint. In order to add data to Table2 I
> need to know the primary key value of the same record in Table1
> before it's committed, which in this case is a serial integer which
> of course is regulated by an implicit index. Does anybody know how
> to access a column's index in the Perl DBI?
> -thanks
Table2 needs to have the foreign key constraint defined as INITIALLY
DEFERRED.
First, turn AutoCommit off (starts a transaction).
Then, get the nextval of the sequence used for the foreign key.
Insert master table row, using the key value.
Insert second table row, again using the key value.
Commit
Set AutoCommit back on, if the execution will continue, and you will need
it again.
I sometimes set up primary key columns like: "id integer PRIMARY KEY
DEFAULT nextval('tblname_seq'),
that way I have a sequence name with a meaningful name, easy to reference.
Susan
Tiered Data Protection Made Simple
http://www.overlandstorage.com/
[GENERAL] PgDBF, Another XBase to PostgreSQL converter
It'd been a couple of years since I wrote a program to convert some random database to PostgreSQL, and it seemed like time to crank out another one. The results, PgDBF, are available under the GPLv3 and downloadable from http://honeypot.net/project/pgdbf . Why yet another program to convert XBase databases in general (and FoxPro in particular) to PostgreSQL? Because the other ones I found were incomplete, complex, slow, or not available on Unix-like systems. We needed something that could run hourly to keep our legacy database in sync with our new production system, and the program we'd been using (XBaseToPg: http://honeypot.net/project/xbasetopg) was a hack on another program that was never meant to be abused that way. Seriously, PgDBF is fast and simple. I profiled it, optimized, profiled, optimized, and profiled again until I couldn't find anything else to tweak. And yet its speed comes primarily through its simplicity, so it should be very easy to maintain. Give it a try. It runs on everything Unixy that I had available for testing (including Linux x86, FreeBSD x86-64, and OS X PPC). I'm pretty pleased with how this turned out. -- Kirk Strauser -- 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] [PERL DBI] Insertiing data across multiple tables
JD Wong wrote: Does anybody know how to insert data over multiple tables transactionally? The relationship is 1:1 with the latter table having a foreign key constraint. In order to add data to Table2 I need to know the primary key value of the same record in Table1 before it's committed, which in this case is a serial integer which of course is regulated by an implicit index. Have a look at INSERT ... RETURNING. It can feed back the new rows to your application so that you can make your 2nd insert without having to pre/reselect them (assuming your running 8.2+) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FK check will use index on referring table?
Hi - My understanding is that PG will use an index on the referring side of a foreign key for FK checks. How can I tell whether it's doing that? EXPLAIN ANALYZE just shows something like this: => explain analyze delete from segments where segmentid = 24305259; QUERY PLAN - Index Scan using segments_pkey on segments (cost=0.00..10.46 rows=1 width=6) (actual time=0.243..0.248 rows=1 loops=1) Index Cond: (segmentid = 24305259) Trigger for constraint $1: time=0.344 calls=1 Trigger for constraint $2: time=0.180 calls=1 Trigger for constraint $1: time=0.325 calls=1 Trigger for constraint tokenizedsegments_segmentid_fkey: time=16910.357 calls=1 Total runtime: 16911.712 ms tokenizedSegments.segmentID has an FK reference to segments.segmentID, and there is an index (not UNIQUE) on the referring column, but the extreme sloth of that last trigger suggests it is not using it. Deferring doesn't matter (perhaps not surprising on one delete). How can I tell what the trigger is doing? I'm using 8.2.5 and I've ANALYZED everything. Thanks. - John D. Burger MITRE -- 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] php + postgresql
On Thursday 24 July 2008 12:41, admin wrote:
> 1.
> I ended up using pg_prepare() and pg_execute() as pg_query() alone just
> didn't seem to work. But SELECT statements seemed to be cached or
> persistent in some way, such that they "lived" beyond the life of the
> PHP script. Is there something I need to know about persistent behaviour
> in PG that doesn't exist in MySQL?
Not sure what causes this with your server but I always use something like
this, ie first connect then do your stuff and then close the connection:
require("dbconnect.inc"); // holds the $conn which is pg_connect("with
passes")
if (!$conn)
{exit("Database connection failed. Please try again." . $conn);}
$sql ="SELECT ...";
$product=pg_exec($conn,$sql);
if (!$product)
{exit("Database connection failed. Please try again.");}
while ($row = pg_fetch_row($product))
{
echo"
$row[1]
";
}
pg_close($conn);
BR,
--
Aarni
Burglars usually come in through your windows.
--
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] FK check will use index on referring table?
* John D. Burger ([EMAIL PROTECTED]) wrote: > My understanding is that PG will use an index on the referring side of a > foreign key for FK checks. How can I tell whether it's doing that? It should, when it makes sense, yes. Having the actual schema definitions would help in debugging this, of course. > EXPLAIN ANALYZE just shows something like this: > > => explain analyze delete from segments where segmentid = 24305259; What does: explain analyze delete from tokenizedSegments where segmentId = 24305259; look like? If more than a few percent of the tokenizedSegments table has a segmentId of 24305259 then PG may rightly be scanning the whole table sequantially because going through it randomly with an index would be slower. There's a few options which can tune those parameters in the planner, of course, but you might consider doing a test 'set enable_seqscan = false;' first, if it's indeed doing one, to see what the difference really is. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] FK check will use index on referring table?
Stephen Frost <[EMAIL PROTECTED]> writes: > * John D. Burger ([EMAIL PROTECTED]) wrote: >> My understanding is that PG will use an index on the referring side of a >> foreign key for FK checks. How can I tell whether it's doing that? There isn't any very good way at the moment :-( > If more than a few percent of the tokenizedSegments table has a > segmentId of 24305259 then PG may rightly be scanning the whole table > sequantially because going through it randomly with an index would be > slower. There's a few options which can tune those parameters in the > planner, of course, but you might consider doing a test > 'set enable_seqscan = false;' first, if it's indeed doing one, to see > what the difference really is. One thing to keep in mind while experimenting is that the plan for an FK update query is cached the first time the particular trigger is fired in a session; and in 8.2 I don't think there's any way to un-cache it short of starting a fresh session. This won't affect manual experimentation of course, but if you do something that you are hoping will change the trigger's behavior (like fooling with enable_seqscan), be sure to start a new session first. Also, the trigger's internal FK query will be parameterized; so the closest manual equivalent will be something like prepare foo(int) as delete from tokenizedSegments where segmentId = $1; explain execute foo(24305259); (adjust datatype of parameter to match segments.segmentID...) and here again remember that "prepare" caches the plan. 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] FK check will use index on referring table?
On Thu, Jul 24, 2008 at 7:06 PM, Stephen Frost <[EMAIL PROTECTED]> wrote: > * John D. Burger ([EMAIL PROTECTED]) wrote: >> My understanding is that PG will use an index on the referring side of a >> foreign key for FK checks. How can I tell whether it's doing that? > > It should, when it makes sense, yes. Having the actual schema > definitions would help in debugging this, of course. > >> EXPLAIN ANALYZE just shows something like this: >> >> => explain analyze delete from segments where segmentid = 24305259; > > What does: > > explain analyze > delete from tokenizedSegments > where segmentId = 24305259; > > look like? If you're doing that put it in a begin; rollback; sandwich so your rows don't disappear while testing. Or just do a explain analyze select 1 from tokenizedSegments where segentId=23405259; the plan would be the same I'd think > > If more than a few percent of the tokenizedSegments table has a > segmentId of 24305259 then PG may rightly be scanning the whole table > sequantially because going through it randomly with an index would be > slower. There's a few options which can tune those parameters in the > planner, of course, but you might consider doing a test > 'set enable_seqscan = false;' first, if it's indeed doing one, to see > what the difference really is. > >Thanks, > >Stephen > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkiJJxIACgkQrzgMPqB3kighnACfd1AaKusTxFaKIqcqEjAmvRwm > LmwAnR0YegtP/rr84LiVVAMJUv3dYOMj > =dPu1 > -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
Re: [GENERAL] php + postgresql
> There does seem to be some evidence of problems historically with PHP > and persistent connections in PostgreSQL, on the PHP forums. The advice > is typically to avoid them. You'll find the same advice for mysql + persistent connections or any other db + persistent connections. It's not a php+postgres thing. -- Postgresql & php tutorials http://www.designmagick.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] php + postgresql
> 2.
> Another problem was that no matter how many times I checked and
> re-checked code, or which pg_fetch_* function I used, copying an array
> member and trying to use it later just would not work, eg
>
> while ($row = pg_fetch_array($query)) {
> $content = $row[0]
> }
>
> echo $content;
pg_fetch_array expects a result from a query, not an actual sql query.
You need something like this:
$query = "select id, name from tablename";
$result = pg_query($query);
while ($row = pg_fetch_array($result)) {
$content = $row[0];
}
> 3.
> Some examples I found used PHP's pg_num_rows() function to count the
> rows in a result, then iterated through them with a "for" loop ... is
> this required behaviour (PHP docs don't appear to discuss this)?
You used to have to do this but you don't any more.
The old style was something like:
4.
> Another weird one was that this statement always failed:
>
> $name = "file.php";
> SELECT fld_content FROM tbl_page WHERE fld_name='$name'
Escape your data:
$name = 'blah';
$query = "SELECT fld_content FROM tbl_page WHERE fld_name='" .
pg_escape_string($name) . "'";
I have some intro guides here you might want to check out:
http://www.designmagick.com/category/2/
--
Postgresql & php tutorials
http://www.designmagick.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] Sequence
Hello.
A table has two primary keys: CREATE TABLE example ( pk1 integer, pk2
integer, PRIMARY KEY (pk1, pk2) ). To add a new record I use command:
INSERT INTO example (pk1, pk2) VALUES (0, 0). Before adding the new
record I have to find out the last value of pk2. How can I use something
like this: INSERT INTO example (pk1, pk2) VALUES (0, nextval('pk2'))?
If a table just has one primary key I can use sequence (CREATE
SEQUENCE). What about two primary keys?
--
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] Sequence
am Fri, dem 25.07.2008, um 15:54:23 +1100 mailte Alex Cheshev folgendes:
> Hello.
> A table has two primary keys: CREATE TABLE example ( pk1 integer, pk2
> integer, PRIMARY KEY (pk1, pk2) ). To add a new record I use command:
> INSERT INTO example (pk1, pk2) VALUES (0, 0). Before adding the new
> record I have to find out the last value of pk2. How can I use something
> like this: INSERT INTO example (pk1, pk2) VALUES (0, nextval('pk2'))?
> If a table just has one primary key I can use sequence (CREATE
> SEQUENCE). What about two primary keys?
You can also use a sequence.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sequence
Alex Cheshev wrote:
Hello.
A table has two primary keys:
It appears that you really meant "a table has a two-field composite
primary key". There can only be one primary key for a table, that's the
whole point - but the primary key can be composed of more than one field.
[Note: if you format your SQL when you post, more people will bother to
read it and try to help you out. For example, your CREATE TABLE could've
been better written as:]
CREATE TABLE example (
> pk1 integer,
> pk2 integer,
PRIMARY KEY (pk1, pk2)
);
To add a new record I use command:
INSERT INTO example (pk1, pk2) VALUES (0, 0).
Before adding the new
record I have to find out the last value of pk2. How can I use something
like this:
INSERT INTO example (pk1, pk2) VALUES (0, nextval('pk2'))
?
If a table just has one primary key I can use sequence (CREATE
SEQUENCE). What about two primary keys?
You can still use a SERIAL type or manually use CREATE SEQUENCE and
nextval() .
I suspect I'm missing the point of your question, though. Perhaps if you
gave a real-world example of what you are trying to do, with meaningful
field names?
--
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
