Hi,
I am running postgreSQL-8.4.2. I have a table that stores a single xml
document per row in one of it's fields. I would like to use xpath to
retrieve portions of these xml documents.
Is there a way to do so. (I am running postgreSQL 8.4.2 configured
(built) with --with-libxml and --with-libxslt
I have limited access to the database. I can not write stored procedures, so it
has to be pure SQL.
But yes, PL opens other possibilities.
Davor
""BillR"" wrote in message
news:004a01caaa01$7b92ade0$72b809...@com...
Is there any reason it has to be done in one DML statement? Can you write
Allan,
Postgres is very strict on variable types and char conversion. I have a
feeling you are trying to access data from a varchar feild using an
integer...
Can you paste here your schema for that table?
P.
On Wed, Feb 10, 2010 at 11:06 AM, Allan Kamau wrote:
> Hi,
> I am running postgreSQL
Hello everybody,
I am quite a novice in using the extension features of the PostgreSQL database.
Actually, I have to do this for work at the university. At the moment, I am
trying around a little bit with creating my own types using shared objects,
written in C. The usage of static types with fi
Davor J. wrote:
Let's say you have a table:
CREATE TABLE t (
time date,
data integer
)
Suppose you want a new table that has columns similar to the following:
"(x.time, x.data, y.time, y.data, z.time, z.data)" where x.time, y.time and
z.time columns are constrained (for example x.time >2007 AN
On Wed, Feb 10, 2010 at 11:34 AM, Otandeka Simon Peter
wrote:
> Allan,
>
> Postgres is very strict on variable types and char conversion. I have a
> feeling you are trying to access data from a varchar feild using an
> integer...
>
> Can you paste here your schema for that table?
>
> P.
>
> On We
As advised by Peter,
Below is an example (including the ddl and dml statements), it _drops_
and creates a table called "simple_table" and a sequence called
"simple_table_seq" both in the "public" schema.
DROP SEQUENCE IF EXISTS simple_table_seq CASCADE;
CREATE SEQUENCE simple_table_seq;
DROP TABLE
If it possible to find out when a table was last vacuumed?
On Wed, Feb 10, 2010 at 8:52 AM, Andy Dale wrote:
> Hi,
>
> I would consider telling Hibernate to log the SQL it is generating to a
> file. This can be done by setting the logging category org.hibernate.SQL to
> debug, and for the parameters used in the prepared statements I think you
> must also
Le 10/02/2010 10:48, AI Rumman a écrit :
> If it possible to find out when a table was last vacuumed?
>
SELECT schemaname, relname, last_vacuum, last_autovacuum
FROM pg_stat_all_tables;
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
--
Sent via pgsql-general mailing list (pgsq
2010/2/10 Martijn van Oosterhout :
>> Can anybody briefly explain me how one postgres process allocate
>> memory for it needs?
>
> There's no real maximum, as it depends on the exact usage. However, in
> general postgres tries to keep below the values in work_mem and
> maintainence_workmem. Most of
Thank you for the input John.
You understood my sketch just fine and your JOIN is indeed equivalent to the
nested select. I said there is no relationship, but in my nested select I
implicitly created a relationship. I should have been more explicit here:
what I meant is that there "should" be n
But I am using Postgresql 8.1 and here no clumn named last_vacuum.
On Wed, Feb 10, 2010 at 4:12 PM, Guillaume Lelarge
wrote:
> Le 10/02/2010 10:48, AI Rumman a écrit :
> > If it possible to find out when a table was last vacuumed?
> >
>
> SELECT schemaname, relname, last_vacuum, last_autovacuum
>
--- On Wed, 10/2/10, AI Rumman wrote:
> If it possible to find out when a table
> was last vacuumed?
Try:
select pg_stat_get_last_vacuum_time(oid) from "pg_catalog".pg_class where
relname = 'tablename';
select pg_stat_get_last_autovacuum_time(oid) from "pg_catalog".pg_class where
relname = '
Carsten Kropf wrote:
The usage of static types with fixed length was actually no problem
for me, so I proceeded to variable length types.
I created an n-dimensional point structure called "PointND" that
contains a field of float8 values of dynamic length. I also put in a
int4/int32 field for t
Actually, I thought, I did this using the int32 variable called "dimension"
which should be exactly this field. Unfortunately, it seems, that something is
wrong here. I'll look inside the code of cube to determine the things I'm doing
wrong, currently. Thanks so far for your advice.
My in-method
Le 10/02/2010 11:23, AI Rumman a écrit :
> But I am using Postgresql 8.1 and here no clumn named last_vacuum.
>
On this old release, the only way you can find such an information is
via the logfile and using DEBUG1 or DEBUG2 level. Not something I would
recommend for a production server.
--
Gu
Carsten Kropf wrote:
Actually, I thought, I did this using the int32 variable called
"dimension" which should be exactly this field.
yes.
in = (PointND *) palloc(sizeof(float8) * dimensions + VARHDRSZ);
SET_VARSIZE(in, dimensions);
What about
len = sizeof(float8) * dimensions + VARHDRSZ;
in =
Oh, I see, does the VARSIZE length field have to be the total number of bytes
occupied (including VARHDRSZ and the size of the structure) or only the size
that is used by "my" datatype? Then it would become pretty much obvious, why
this is not supposed to work.
I'll try it out then.
regards
On Wed, Feb 10, 2010 at 6:59 AM, A. Kretschmer
wrote:
> test=*# analyse table_a;
> ERROR: canceling autovacuum task
> CONTEXT: automatic vacuum of table "test.public.table_a"
> ANALYZE
> Time: 1235,600 ms
>
>
> I think, that's not an ERROR, just a NOTICE for me. And yes, the
> transaction isn't
Carsten Kropf wrote:
Oh, I see, does the VARSIZE length field have to be the total number of bytes occupied
(including VARHDRSZ and the size of the structure) or only the size that is used by
"my" datatype?
Yes
Then it would become pretty much obvious, why this is not supposed to work.
I'll
In response to Greg Stark :
> On Wed, Feb 10, 2010 at 6:59 AM, A. Kretschmer
> wrote:
> > test=*# analyse table_a;
> > ERROR: canceling autovacuum task
> > CONTEXT: automatic vacuum of table "test.public.table_a"
> > ANALYZE
> > Time: 1235,600 ms
> >
> >
> > I think, that's not an ERROR, just a
Hi,
A long-standing problem we've had with PostgreSQL queries in PHP is
that the returned data for boolean columns is the string 'f' instead
of the native boolean value of false.
An obvious example of this would be for a table with users and their
boolean registered status:
Select user, register
In response to Thom Brown :
> Hi,
>
> A long-standing problem we've had with PostgreSQL queries in PHP is
> that the returned data for boolean columns is the string 'f' instead
> of the native boolean value of false.
http://andreas.scherbaum.la/blog/archives/302-BOOLEAN-datatype-with-PHP-compatib
Thanks for the hint according to the cube, this was actually exactly what I
have been looking for. I wanted to do something similar like the cube but I
didn't think that it would be implemented in multiple dimension. I just
thought, the cube were a 3-d construct, but as I see in the sources, it
Thom Brown wrote:
Is this a limitation of libpq or a flawed implementation in the php
library? And if this is just the case for backwards-compatibility, is
there a way to switch it to a more sensible PHP data type?
Using PDO(http://no.php.net/pdo) will at least give you native values
for tru
On 10 February 2010 12:11, A. Kretschmer
wrote:
> In response to Thom Brown :
>> Hi,
>>
>> A long-standing problem we've had with PostgreSQL queries in PHP is
>> that the returned data for boolean columns is the string 'f' instead
>> of the native boolean value of false.
>
> http://andreas.scherba
Is there an SQL function to determine the size of a large object?
Also, can I safely delete all the large objects in
pg_catalog.pg_largeobject? For example:
select lo_unlink(loid) from (select distinct loid from
pg_catalog.pg_largeobject) as loids where loid not in (select my_oid
from my_onl
On Tue, Feb 2, 2010 at 12:19 AM, Tom Lane wrote:
> frank joerdens writes:
>> It seems that whenever I create a new empty table with a foreign key
>> constraint, the transaction will acquire an exclusive lock on the
>> referenced table, locking out other writers (not sure if even readers
>> as wel
Hello list,
The Orafce compatibility package doesn't seem to have operators defined
(looked in the sql load file). The function I'm specifically interested
in, is Oracle's concatenation that regards a NULL as the empty string
and hence returns 'the other value'. This in contrast with Pg's || t
John R Pierce wrote:
how do you plan on accessing this monster data? do you expect to be
looking up single values or small set of values at a specific time?
seems to me like this is the sort of data thats more often processed in
the aggregate, like running a fourier analysis of sliding windows
2010/2/10 Yeb Havinga :
> Hello list,
>
> The Orafce compatibility package doesn't seem to have operators defined
> (looked in the sql load file). The function I'm specifically interested in,
> is Oracle's concatenation that regards a NULL as the empty string and hence
> returns 'the other value'.
Richard Huxton wrote:
On 09/02/10 11:25, Ben Campbell wrote:
[I was talking about moving a "needs_indexing" flag out of a big table
into it's own table]
But my gut feeling is that the flag would be better off in it's own
table anyway, eg:
CREATE TABLE needs_indexing (
article_id integer refere
On Wed, Feb 10, 2010 at 2:32 PM, Asher wrote:
> The data will initially be accessed via a simple GUI which will allow
> browsing over a subset of the data (subsampled down to 1 sample/minute/hour,
> etc.
It sounds like you could use a tool like rrd that keeps various levels
of aggregation and int
Martijn van Oosterhout writes:
> On Tue, Feb 09, 2010 at 08:19:51PM +0500, Anton Maksimenkov wrote:
>> Can anybody briefly explain me how one postgres process allocate
>> memory for it needs?
> There's no real maximum, as it depends on the exact usage. However, in
> general postgres tries to keep
El 10/02/2010 6:49, Scott Marlowe escribió:
Quick note, please stick to text formatted email for the mailing list,
it's the preferred format.
On Tue, Feb 9, 2010 at 9:09 PM, Jayadevan M
wrote:
Hello all,
Apologies for the long mail.
I work for a company that is provides solutions mostly o
Jayadevan M wrote:
> Could some of you please share some info on such scenarios- where
> you are supporting/designing/developing databases that run into at
> least a few hundred GBs of data (I know, that is small by todays'
> standards)?
I'm a database administrator for the Wisconsin Courts.
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote:
> > Could some of you please share some info on such scenarios- where
> > you are supporting/designing/developing databases that run into at
> > least a few hundred GBs of data (I know, that is small by todays'
> > standards)?
Just saw this, so
Pavel Stehule wrote:
What about adding something like operator ||| in the orafce package for
concat?
no, it could be confusing and it isn't enough, because it isn't only
|| or concat problem. On Oracle empty string is equal to NULL and NULL
is equal to empty string.
example: '' is null, l
2010/2/10 Yeb Havinga :
> Pavel Stehule wrote:
>>>
>>> What about adding something like operator ||| in the orafce package for
>>> concat?
>>>
>>
>> no, it could be confusing and it isn't enough, because it isn't only
>> || or concat problem. On Oracle empty string is equal to NULL and NULL
>> is e
On Wed, Feb 10, 2010 at 1:21 AM, Scott Marlowe wrote:
>
> On Wed, Feb 10, 2010 at 12:11 AM, Steve Atkins wrote:
> > A database isn't really the right way to do full text search for single
> > files that big. Even if they'd fit in the database it's way bigger than the
> > underlying index types
Kevin Grittner (kevin.gritt...@wicourts.gov) wrote:
Could some of you please share some info on such scenarios- where
you are supporting/designing/developing databases that run into at
least a few hundred GBs of data (I know, that is small by todays'
standards)?
At NuevaSync we use PG in
> As I mentioned on the list a couple of months ago we are in the middle
> of stuffing a bunch of molecular data (including entire genomes) into
> Postgres. If anyone else is doing this I would welcome the
> opportunity to discuss the issues off list...
>
>
I do not stuff molecules or genomes or
create table foo (name text, company text, job text);
insert into foo (name,company,job) values ('joe','ge','engineer');
insert into foo (name) values ('sue');
What I want to do is map joe's company and job over to the sue record, ending
up with
'sue' 'ge' 'engineer'
Is there a quick/clever
2010/2/10 Greg Stark :
> On Wed, Feb 10, 2010 at 2:32 PM, Asher wrote:
>> The data will initially be accessed via a simple GUI which will allow
>> browsing over a subset of the data (subsampled down to 1 sample/minute/hour,
>> etc.
>
> It sounds like you could use a tool like rrd that keeps variou
On 10 Feb 2010, at 17:28, Gauthier, Dave wrote:
> create table foo (name text, company text, job text);
> insert into foo (name,company,job) values (‘joe’,’ge’,’engineer’);
> insert into foo (name) values (‘sue’);
>
> What I want to do is map joe’s company and job over to the sue record, ending
Outstanding !
Thanks Alban.
-Original Message-
From: Alban Hertroys [mailto:dal...@solfertje.student.utwente.nl]
Sent: Wednesday, February 10, 2010 11:46 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] need clean way to copy col vals from one rec to another
I'm writing a log parser front-end. I've seen some log samples that
look like this, with the duration and statement on the same line:
LOG: duration: 1.565 ms statement: SELECT * FROM users WHERE user_id='692'
But in my 8.3.9 test installation, durations are always logged on a
separate line. I
On Wed, Feb 10, 2010 at 12:55:03PM -0500, Baron Schwartz wrote:
> I'm writing a log parser front-end. I've seen some log samples that
> look like this, with the duration and statement on the same line:
>
> LOG: duration: 1.565 ms statement: SELECT * FROM users WHERE user_id='692'
>
> But in my
Baron Schwartz writes:
> I'm writing a log parser front-end. I've seen some log samples that
> look like this, with the duration and statement on the same line:
> LOG: duration: 1.565 ms statement: SELECT * FROM users WHERE user_id='692'
> But in my 8.3.9 test installation, durations are alwa
Tom Lane wrote:
Martijn van Oosterhout writes:
On Tue, Feb 09, 2010 at 08:19:51PM +0500, Anton Maksimenkov wrote:
Can anybody briefly explain me how one postgres process allocate
memory for it needs?
There's no real maximum, as it depends on the exact usage. However, in
ge
Thanks Tom, Depesz,
On Wed, Feb 10, 2010 at 1:15 PM, Tom Lane wrote:
> turn off log_statement and instead set
> log_min_duration_statement = 0 to log everything via duration logging.
That does the trick. Time to write more test cases.
Thanks
Baron
--
Sent via pgsql-general mailing list (pgsq
Thom Brown wrote:
Hi,
A long-standing problem we've had with PostgreSQL queries in PHP is
that the returned data for boolean columns is the string 'f' instead
of the native boolean value of false.
An obvious example of this would be for a table with users and their
boolean registered status:
S
Before Installed postgresql Windows7 went straight to my user account.
Now when it boots I have to go to a selection page where I choose between my
user account and a postgresql user account. Is there any way to bypass this so
it boots directly to my user account?
Hi,
Hmm. Things are still getting interesting around here.
Here is my complicated problem. I tried to delete a user from my users table,
but it said
"ERROR: bug_assigned_to_fk referential integrity violation - key in users
still referenced from bug"
Ok.
Then I saw this statement in the .sql f
On Wednesday 10 February 2010 4:56:21 pm Wang, Mary Y wrote:
> Hi,
>
> Hmm. Things are still getting interesting around here.
>
> Here is my complicated problem. I tried to delete a user from my users
> table, but it said "ERROR: bug_assigned_to_fk referential integrity
> violation - key in users
Yes. I do want to keep that information in the bug : assigned_to=user_id
Yes. I'm embrassed to say it's still in 7.1.3. I know, I know that I need to
upgrade, but I do need to fix a problem now.
\d+ bug returns
Table "bug"
Attribute | Type |
I found it's pg_compresslog problem (calculation of XNOOP record
length used in pg_decompresslog).I'm fixing the bug and will
upload the fix shortly.
Sorry for inconvenience.
--
Koichi Suzuki
2010/2/8 Karl Denninger :
> This may belong in a bug report, but I'll post it here f
"Wang, Mary Y" writes:
> Here is my complicated problem. I tried to delete a user from my users
> table, but it said
> "ERROR: bug_assigned_to_fk referential integrity violation - key in users
> still referenced from bug"
> Ok.
> Then I saw this statement in the .sql file.
> "CREATE CONSTRAINT
Ok. I typed the correct name this time, and got the same error.
"drop trigger bug_assigned_to_fk on users;
ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation users "
"drop trigger bug_assigned_to_fk on bug;
ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relatio
On Wednesday 10 February 2010 7:07:08 pm Wang, Mary Y wrote:
> Ok. I typed the correct name this time, and got the same error.
> "drop trigger bug_assigned_to_fk on users;
> ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation
> users " "drop trigger bug_assigned_to_fk on bug;
>
"Wang, Mary Y" writes:
> Ok. I typed the correct name this time, and got the same error.
> "drop trigger bug_assigned_to_fk on users;
> ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation users
> "
> "drop trigger bug_assigned_to_fk on bug;
> ERROR: DropTrigger: there is no
Wow!! Good memory.
I didn't get anything with the 'select * from pg_trigger where tgname =
'bug_assigned_to_fk';' but
I got something here :
select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk';
tgrelid |tgname| tgfoid | tgtype | tgenabled |
tgisconstr
I have a strange problem we noticed the other day with triggers. We're
running 8.3.3 on Solaris 10 (intel) and have a feed that comes in
regularly to populate a table we're working on. The feed works just
fine inserting rows however the following trigger stops the feed until
we remove the tri
I guess I could just use
"DELETE FROM pg_trigger WHERE tgname = "RI_ConstraintTrigger_9217018"
I just wanted to make sure that I don't mess up the system table.
Mary
-Original Message-
From: Wang, Mary Y
Sent: Wednesday, February 10, 2010 8:10 PM
To: 'Tom Lane'
Cc: pgsql-general@postgres
"Wang, Mary Y" writes:
> I got something here :
> select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk';
> tgrelid |tgname| tgfoid | tgtype | tgenabled |
> tgisconstr aint |tgconstrname| tgconstrrelid |
> tgdeferrable | tginitdeferr
Jeff Ross wrote:
pgbench is run with this:
pgbench -h varley.openvistas.net -U _postgresql -t 2 -c $SCALE pgbench
with scale starting at 10 and then incrementing by 10. I call it
three times for each scale. I've turned on logging to 'all' to try
and help figure out where the system panics
Ben Chobot wrote:
I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup.
Shouldn't that be at least fairly close to (n_tup_ins - n_tup-del)? It doesn't
seem to be, but I'm unclear why.
Insert 2000 tuples.
Delete 1000 tuples.
vacuum
Insert 1000 tuples. These go into
Greg Smith writes:
> Ben Chobot wrote:
>> I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about
>> n_live_tup. Shouldn't that be at least fairly close to (n_tup_ins -
>> n_tup-del)? It doesn't seem to be, but I'm unclear why.
>>
> Insert 2000 tuples.
> Delete 1000 tuples.
> vacuum
Thom Brown schrieb:
A long-standing problem we've had with PostgreSQL queries in PHP is
that the returned data for boolean columns is the string 'f' instead
of the native boolean value of false.
This problem is solved since nearly 5 years with PDO. You can use an
abstraction like DDDBL (see my
Thanks a lot so far. I adopted my structures and am now storing two fields
(v_len_ and dimensions) and the storage is now working properly. If I now would
try to combine two of these points to a range (like cube) including an upper
and a lower bound n-dimensional point structure, I don't get the
71 matches
Mail list logo
