Tom-right-as-usual:
Yep - you were right about the query plan for the prepared statement (a
sequential scan of the table) differed a bit from the directly-executed
version :)
For reference, when using JasperReports .jrxml files as the basis for
the query, I only had to do to the following to
"Jason L. Buberel" <[EMAIL PROTECTED]> writes:
> In my syslog output, I see entries indicating that the
> JDBC-driver-originated query on a table named 'city_summary' are taking
> upwards of 300 seconds:
> Oct 1 18:27:47 srv3 postgres-8.2[1625]: [12-1]
> LOG: duration: 307077.037 ms execute S
I'm hoping that someone on the list can help me understand an apparent
discrepancy in the performance information that I'm collecting on a
particularly troublesome query.
The configuration: pg-8.2.4 on RHEL4. log_min_duration_statement = 1m.
In my syslog output, I see entries indicating that t
Magnus Hagander escribió:
On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote:
First of all. I think this method is admisible. Isn't it ?
It is.
Glad to read it :-)
And second question: I think my problem is that some rights are wrong
after copying data folder. What a
\d my.table is showing me the primary key as:
"unitstat_pkey" PRIMARY KEY, btree (id)
But the looking into the table structure produced by
pg_dump -s -n my -t table db
I'm getting gthe primary key shown as:
ADD CONSTRAINT unistat_pkey PRIMARY KEY (id);
That has been the name of it, but I alte
On 01/10/2007, Martin Marques <[EMAIL PROTECTED]> wrote:
>
> 1) Is MD5's weakness true?
Yes, but not really for using in a password functionality. You are
very unlikely to repeat a password but in any case you will have the
user ID to make it unique.
> 2) Is there any sha1() functions in Postgr
On 10/1/07, Goboxe <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Are there any limitations on number of child tables that can be use
> in
> partitioned table?
>
>
> I am currently having weekly partitioned tables (using partitioned
> view in SQL Server) that I kept for 2 years.
> In total, there will be 52
On Oct 1, 2007, at 14:54 , Farhan Khan wrote:
Any pointer for implementing this functionality in postgresql ??
Write a trigger that fires on update and replaces the value with
CURRENT_TIMESTAMP.
You could probably do something like this with rules as well, but the
trigger method is prob
On 9/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> I don't care if it's part of the SQL standard or not. I don't care if
> oracle does it or not. You're losing mysql converts as they go
> through the tutorial and get to this point.
If that's all it takes for them to switch, seriously, I
Hi ... I am porting a db schema from MySQL to postgresql and having problem
in defining a column level constraint...
mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
gives me problem at ^ON UPDATE ...
Any pointer for implementing this functionality in postgresql ??
Tx ..
Josh Trutwin wrote:
> Is it possible to somehow query the system catalog to find out which
> clusters/databases have a certain schema?
No, unless you connect to each database in turn.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command
Hi,
Le Wednesday 26 September 2007 20:58:38 Gowrishankar, vous avez écrit :
> Is there any other way of extending postgresql to include cubes?
Something like the cube contrib?
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/cube/
Contribs are part of PostgreSQL sources and maintained
Hi,
Le Friday 28 September 2007 10:22:49 [EMAIL PROTECTED], vous avez
écrit :
> I need to import between 100 millions to one billion records in a
> table. Each record is composed of two char(16) fields. Input format
> is a huge csv file.I am running on a linux box with 4gb of ram.
> First I crea
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Matthew T. O'Connor wrote:
> Joshua D. Drake wrote:
>> The PostgreSQL Conference Fall 2007 to date, has been a huge success. Of
>> course we haven't actually held the conference yet but already we have a
>> strong line of speakers and sponsors confirme
Joshua D. Drake wrote:
The PostgreSQL Conference Fall 2007 to date, has been a huge success. Of
course we haven't actually held the conference yet but already we have a
strong line of speakers and sponsors confirmed.
[ snip ]
I can't attend, but wish I could, is there going to be a web cast? O
thanks for all your useful comments. i will study all of them.
a couple of inline comments below, just for clarification to the group,
marked with asterisks.
On Mon, 1 Oct 2007 13:13:23 -0500, "Scott Marlowe"
<[EMAIL PROTECTED]> said:
> On 10/1/07, Jan Theodore Galkowski <[EMAIL PROTECTED]> wrot
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello,
The PostgreSQL Conference Fall 2007 to date, has been a huge success. Of
course we haven't actually held the conference yet but already we have a
strong line of speakers and sponsors confirmed.
If you haven't registered, now is your chance, ju
On 10/1/07, Jan Theodore Galkowski <[EMAIL PROTECTED]> wrote:
> Scott,
>
> i didn't think this belonged in the general list, but the example i gave
> for discussion was a toy, for illustration. i could not very well post
> the actual example for many reasons, including proprietary ones and,
> give
On Oct 1, 2007, at 12:26 PM, Gauthier, Dave wrote:
I’m going to move from v7.4.13 to v8.2.0 (suse-64). I have 2 pre-
existing DBs. Do I need to “convert” or port them to v8 in any way
after I start up with a v8 postmaster?
Thanks
Moving between major release versions requires that you do
"Mike Charnoky" <[EMAIL PROTECTED]> writes:
> Here is the output from EXPLAIN ANALYZE. This is the same query run
> back to back, first time takes 42 minutes, second time takes less than 2
> minutes!
That doesn't really sound strange at all. It sounds like you have a very slow
disk and very lar
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Gauthier, Dave wrote:
> I'm going to move from v7.4.13 to v8.2.0 (suse-64). I have 2
> pre-existing DBs. Do I need to "convert" or port them to v8 in any way
> after I start up with a v8 postmaster?
>
1. v8.2.0 is a mistake, make sure you are runn
On Monday 01 October 2007, "Gauthier, Dave" <[EMAIL PROTECTED]> wrote:
> I'm going to move from v7.4.13 to v8.2.0 (suse-64). I have 2
> pre-existing DBs. Do I need to "convert" or port them to v8 in any way
> after I start up with a v8 postmaster?
>
All major version upgrades require a dump and r
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Goboxe
> Sent: Monday, October 01, 2007 2:18 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Partitioned table limitation
>
> Hi,
>
> Are there any limitations on number of c
Mike Charnoky wrote:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query again, it finishes in 1-2 minutes!
This is just
In response to Mike Charnoky <[EMAIL PROTECTED]>:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query again, it finishes
I'm going to move from v7.4.13 to v8.2.0 (suse-64). I have 2
pre-existing DBs. Do I need to "convert" or port them to v8 in any way
after I start up with a v8 postmaster?
Thanks
-dave
On 10/1/07, pere roca ristol <[EMAIL PROTECTED]> wrote:
> Hi everybody,
> I want to enter a .CSV file using COPY comand and plpgsql.
> It enters latitude,longitude and some data. In the CSV data there is no
> field (such as "user_name" or current_time) that allow distinguish future
> queries
On Monday 01 October 2007, Mike Charnoky <[EMAIL PROTECTED]> wrote:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query a
Hi!
I have this problem that Im not sure if my stored data has the correct
coding. When I view the data from a postgres console I just see the
characters and depending on my console-encoding it looks differently.
What I really want to see is the hexadecimal or octal value of the bytes of
the ret
Hi,
Are there any limitations on number of child tables that can be use
in
partitioned table?
I am currently having weekly partitioned tables (using partitioned
view in SQL Server) that I kept for 2 years.
In total, there will be 52 * 2 = 104 tables exist at one time in the
partition.
I am mig
Hello,
I need to import between 100 millions to one billion records in a
table. Each record is composed of two char(16) fields. Input format
is a huge csv file.I am running on a linux box with 4gb of ram.
First I create the table. Second I 'copy from' the cvs file. Third I
create the index on the
Thanks for your answers guys. I've got a cold right now and my brain
is mush, so I can't comment intelligently on your suggestions just
yet. I just wanted to express my thanks for your time.
Jeff, one book you might want to look at is Joe Celko's Trees and
Hierarchies in SQL for Smarties.
http://
I am now importing the dump file of wikipedia into my postgresql using
maintains/importDump.php. It fails on 'ERROR: invalid byte sequence
for encoding UTF-8'. Is there any way to let pgsql just ignore the
invalid characters ( i mean that drop the invalid ones ), that the
script will keep going wit
Is it possible to somehow query the system catalog to find out which
clusters/databases have a certain schema?
When running the query:
SELECT * FROM pg_catalog.pg_namespace WHERE nspname = 'myschema';
It always only finds data for the current session, not all clusters,
even when connected as pos
Hi,
> /var/run/ might be on a temporary file system. So you need to adjust
> your init script to create that directory if it doesn't exist.
That is what I tried now and it works for now.
I never had installed the Debian's PostgreSQL packages and I once
manually installed those init.d-script. As
Hi All,
I want to implement data cube operator in PostGReSQL. I searched few
forums and found that I only can have interface to postgresql using
EFEU package which allows basic cube operations.
Is there any other way of extending postgresql to include cubes?
thanks
Gowrishankar
---
I don't care if it's part of the SQL standard or not. I don't care if
oracle does it or not. You're losing mysql converts as they go
through the tutorial and get to this point. Or worse, they just "grant
all" because it's easier, thus causing security holes. User
friendliness matters.
There's l
This is strange... count(*) operations over a period of one day's worth
of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
first time the data is queried it takes about 40 minutes. If I try the
query again, it finishes in 1-2 minutes!
Again, nothing else is happening on this
Gregory Stark ha scritto:
"Nico Sabbi" <[EMAIL PROTECTED]> writes:
nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
ERROR: missing FROM-clause entry for table "r"
LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
^
I tried many variations (in
Webb Sprague escribió:
> > > Is it
> > > possible to have FK that spans into child tables?
> >
> > This is a well known (and documented, see [1]) deficiency. It's due to
> > the current implementation of indices, which are bound to exactly one
> > table, meaning they do return a position within
"Sebastjan Trepca" <[EMAIL PROTECTED]> writes:
> Current state:
> Table B has a primary key with sequence b_seq. Table A also has a
> primary key with sequence a_seq.
In view of the fact that primary keys aren't inherited, and do not
"have sequences", this description is uselessly imprecise. Ple
Hi,
I noticed a small bug/problem when restoring a database that uses inheritance.
Lets say you have a table B that inherits from table A.
Current state:
Table B has a primary key with sequence b_seq. Table A also has a
primary key with sequence a_seq.
Now we create a backup and restore the da
Stefan Schwarzer wrote:
>
>> An entirely different question is whether it is a good idea to write a
>> range as a value that the database cannot interpret correctly (referring
>> to the '1970-75' notation). You cannot group records by value this way
>> if you need to (for example) combine data fro
On 9/27/07, Jan Theodore Galkowski <[EMAIL PROTECTED]> wrote:
> I fear this has been asked many times about PostgreSQL, and I have read
> the docs about how indexes are supposed to be defined and used, but I
> don't understand why the engine and optimizer is doing what it does in
> the simplest of
"Mike Charnoky" <[EMAIL PROTECTED]> writes:
> I altered the table in question, with "set statistics 100" on the
> timestamp column, then ran analyze. This seemed to help somewhat. Now,
> queries don't seem to hang, but it still takes a long time to do the count:
> * "where evtime between '2007-
An entirely different question is whether it is a good idea to write a
range as a value that the database cannot interpret correctly
(referring
to the '1970-75' notation). You cannot group records by value this way
if you need to (for example) combine data from '1970' with data from
'1970-75'
I altered the table in question, with "set statistics 100" on the
timestamp column, then ran analyze. This seemed to help somewhat. Now,
queries don't seem to hang, but it still takes a long time to do the count:
* "where evtime between '2007-09-26' and '2007-09-27'"
took 37 minutes to run (r
Stefan Schwarzer wrote:
>
>
>> BTW, You didn't actually use type text for your year column, did you? No
>> quotes needed then. Otherwise you'd have to make sure your year values
>> are all the same length or sorting gets... interesting.
>
> Yep, my comment just before concerns especially this pa
Martin Marques wrote:
We are at the moment planning on passing some passwords that are plain
texted in our DB to some encrypted form as now they will be used for
processes that require better security measures.
We started looking at md5() but found that it's easy to crack and one of
the syste
I find it far easier to maintain normalized tables that produced
non-normalized ones (for things like data warehousing) than it is to
maintain non-normalized tables and trying to produce normalized data
from that.
Ok, I do understand that.
So, instead of the earlier mentioned database design, I
BTW, You didn't actually use type text for your year column, did
you? No
quotes needed then. Otherwise you'd have to make sure your year values
are all the same length or sorting gets... interesting.
Yep, my comment just before concerns especially this paragraph, I
guess. With not only ye
Hi,
\d my.table is showing me the primary key as:
"unitstat_pkey" PRIMARY KEY, btree (id)
But the looking into the table structure produced by
pg_dump -s -n my -t table db
I'm getting gthe primary key shown as:
ADD CONSTRAINT unistat_pkey PRIMARY KEY (id);
That has been the name of it, but I a
- one table for the years names/ids/etc. (1970, 1; 1971, 2;
1973, 3;
)
If you _do_ need this table (because you want to constrain your
statistical data to only contain a specific set of years, or
because you
need a quick list of available years to select from): Make the year
pri
We are at the moment planning on passing some passwords that are plain
texted in our DB to some encrypted form as now they will be used for
processes that require better security measures.
We started looking at md5() but found that it's easy to crack and one of
the systems uses CHAP authentica
> Is this set to be fixed in any particular release?
Depending on what you're doing, this may be overkill, but: I have child
tables that not only need FK constraints, but also triggers and the
functions called by the triggers. So instead of writing this over and over
again, I eventually wrote a si
Albe Laurenz wrote:
> Alban Hertroys wrote:
>> A. Kretschmer wrote:
>>> Again: an index can't help! Because of MVCC: 'select count(*)'
> without
>>> WHERE-condition forces an seq. table-scan.
>> That has very little to do with MVCC.
>>
>> [...] For that it makes no difference whether a seq
>> scan
Sequence scans of an empty table are going to be faster than an index scan,
so the database uses the sequence scan. Put some data in the tables (some
thousands or millions of records) and then see if it uses an index scan.
Ben
""Jan Theodore Galkowski"" <[EMAIL PROTECTED]> wrote in message
ne
Albe Laurenz skrev:
> Anoo Sivadasan Pillai wrote:
>> Why the Fun_ABC1 is created and Fun_ABC12 is raising the
>> following error, while run through psql, ( I Could create
>> both the functions from PgAdmin III query )
>>
>> ERROR: invalid byte sequence for encoding "UTF8": 0x93
>
> Because th
Naz Gassiep wrote:
> Aside from the messy nomenclature, is anyone able to spot why
> the "sum" column from the first query is not returning 7, as
> the second query suggests that it should? I know that this is
> probably simple, and that It's probably going to jump out at
> me the minute I hit "S
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> I don't care if it's part of the SQL standard or not. I don't care if
> oracle does it or not. You're losing mysql converts as they go
> through the tutorial and get to this point. Or worse, they just "grant
> all" because it's easie
Anoo Sivadasan Pillai wrote:
> Why the Fun_ABC1 is created and Fun_ABC12 is raising the
> following error, while run through psql, ( I Could create
> both the functions from PgAdmin III query )
>
> ERROR: invalid byte sequence for encoding "UTF8": 0x93
Because the characters you entered into
Aside from the messy nomenclature, is anyone able to spot why
the "sum" column from the first query is not returning 7, as
the second query suggests that it should? I know that this is
probably simple, and that It's probably going to jump out at
me the minute I hit "Send", but if I don't hit sen
On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote:
> Hello all,
>
> I would like to change a pg database to another server.
>
> The source environment is: postgresql Windows v.8.2.4 (windows xp
> workstation).
> The target environment is: postgresql Windows v.8.2.5 (windows 2003 Server)
Anoo Sivadasan Pillai wrote:
Why the Fun_ABC1 is created and Fun_ABC12 is raising the following
error, while run through psql, ( I Could create both the functions from
PgAdmin III query )
ERROR: invalid byte sequence for encoding "UTF8": 0x93
HINT: This error can also happen if the byte seq
Alban Hertroys wrote:
> A. Kretschmer wrote:
>> Again: an index can't help! Because of MVCC: 'select count(*)'
without
>> WHERE-condition forces an seq. table-scan.
>
> That has very little to do with MVCC.
>
> [...] For that it makes no difference whether a seq
> scan or an index scan is perform
"Alban Hertroys" <[EMAIL PROTECTED]> writes:
> Mike Charnoky wrote:
>> With respect to the ALTER TABLE SET STATISTICS... how do I determine a
>> good value to use? This wasn't really clear in the pg docs. Also, do I
>> need to run ANALYZE on the table after I change the statistics?
>>
>> Here a
Why the Fun_ABC1 is created and Fun_ABC12 is raising the following
error, while run through psql, ( I Could create both the functions from
PgAdmin III query )
ERROR: invalid byte sequence for encoding "UTF8": 0x93
HINT: This error can also happen if the byte sequence does not match
the enco
On Monday 01 October 2007 05:20:52 pere roca wrote:
> Hi everybody,
> I want to enter a .CSV file using COPY comand and plpgsql. It enters
> lat,lon and some data. In the CSV data there is no field (such as
> "user_name" or current_time) that allow distinguish future queries for
> different use
rihad wrote:
> Richard Broersma Jr wrote:
> UPDATE Foo foo
> SET ...
> FROM LEFT JOIN Bar bar USING(common_field)
> WHERE blah='blah' AND bar.common_field IS NULL;
>
> ERROR: syntax error at or near "JOIN"
>
>
> I know I'm misusing UPDATE ... FROM because I don't really want Bar's
> values to g
Mike Charnoky wrote:
> With respect to the ALTER TABLE SET STATISTICS... how do I determine a
> good value to use? This wasn't really clear in the pg docs. Also, do I
> need to run ANALYZE on the table after I change the statistics?
>
> Here are the EXPLAINs from the queries:
>
> db=# explain s
A. Kretschmer wrote:
> Again: an index can't help! Because of MVCC: 'select count(*)' without
> WHERE-condition forces an seq. table-scan.
That has very little to do with MVCC.
If I understand correctly, MVCC is about the availability of records in
the current transaction. For that it makes no d
Hi everybody,
I want to enter a .CSV file using COPY comand and plpgsql. It enters
lat,lon and some data. In the CSV data there is no field (such as
"user_name" or current_time) that allow distinguish future queries for
different users (ex: select x,y from table where user_name=z; after enteri
Hi everybody,
I want to enter a .CSV file using COPY comand and plpgsql.
It enters latitude,longitude and some data. In the CSV data there is no
field (such as "user_name" or current_time) that allow distinguish future
queries for different users (ex: select x,y from table where user_name=z;
73 matches
Mail list logo
