On 2012-04-24, rihad wrote:
> As PostgreSQL stores timestamps with a fractional part, does it mean that
> WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:59' might miss
> records with values of f equal to 23:59:59.1234 or so?
yes, it does. BETWEEN doesn't work well for timestamps.
you
What about using
WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'?
On 04/25/2012 09:52 AM, Jasen Betts wrote:
On 2012-04-24, rihad wrote:
As PostgreSQL stores timestamps with a fractional part, does it mean that
WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:59'
On Tue, Apr 24, 2012 at 10:04 PM, Thom Brown wrote:
> What was the experience? Is it possible you had specified a
> compression level without the format set to custom? That would result
> in a plain text output within a gzip file, which would then error out
> if you tried to restore it with pg_
On Wed, Apr 25, 2012 at 09:42, Willy-Bas Loos wrote:
> On Tue, Apr 24, 2012 at 10:04 PM, Thom Brown wrote:
>>
>> What was the experience? Is it possible you had specified a
>> compression level without the format set to custom? That would result
>> in a plain text output within a gzip file, whi
Stacking views is a bad practice. It usually means that you are making the
db do a lot of unnecessary work, scanning tables more than once when you
don't even need them.
According to your description, you have 3 layers of views on partitioned
tables.
I can imagine that that leaves the planner with
On Tue, 2012-04-24 at 12:10 +0200, Thomas Kellerer wrote:
> Rafal Pietrak, 24.04.2012 09:02:
> >
> > is not an option, since the function is *very* expensive (multiple join
> > of large tables - inventories, history, etc).
> >
> > Is there a syntax workaround that I could possibly use to get the ef
Andreas writes:
> How would I group the table so that it shows groups that have
> similarity () > x ?
>
> Lets say the table looks like this:
>
> id, txt
> 1, aa1
> 2, bb1
> 3, cc1
> 4, bb2
> 5, bb3
> 6, aa2
> ...
>
> How would a select look like that shows:
>
> id, txt, group_
Andreas writes:
> How would I group the table so that it shows groups that have
> similarity () > x ?
>
> Lets say the table looks like this:
>
> id, txt
> 1, aa1
> 2, bb1
> 3, cc1
> 4, bb2
> 5, bb3
> 6, aa2
> ...
>
> How would a select look like that shows:
>
> id, txt, group_
On Wed, Apr 25, 2012 at 9:51 AM, Magnus Hagander wrote:
> We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as
> well, IIRC, specifically on Win32. Maybe you were hit by that one..
Yes, possibly. I didn't even know how to make a compressed plain dump, but
that doesn't really plea
Hi Willy-Bas,
Thanks for your reply.
I realise that stacking the views up like this complicates matters, but the
actual views are fairly simple queries, and each one individually is only
looking at a few dozen rows. (Eg. selecting min, max or average value from a
small set, grouped by one colum
Best Regards,
Abbas
On Wed, Apr 25, 2012 at 11:16 AM, Akhila Banu Rumi <
akhilabanu_r...@infosys.com> wrote:
> Really Heart touching …
>
> ** **
>
> *From:* Amey Ratnakar Prabhu
> *Posted At:* Wednesday, April 25, 2012 9:16 AM
> *Posted To:* HYD General
> *Conversation:* Really heart touch
would it be possible to reproduce the same query without using any views?
you could see the difference in memory usage.
if that doesn't explain, try also without inheritance, by using the ONLY
keyword (and UNION ALL).
If it's really only a couple of rows, you might as well post a dump
somewhere?
On 2012-04-25, Valentin Militaru wrote:
> This is a multi-part message in MIME format.
> --050404030901030607030308
> Content-Type: text/plain; charset=UTF-8; format=flowed
> Content-Transfer-Encoding: 7bit
>
> What about using
>
> WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24
- Original Message -
> From: "Willy-Bas Loos"
> To: "Toby Corkindale"
> Cc: "pgsql-general"
> Sent: Wednesday, 25 April, 2012 7:16:50 PM
> Subject: Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
>
> would it be possible to reproduce the same query without using any
>
2012/4/25 Jasen Betts :
> On 2012-04-25, Valentin Militaru wrote:
>> This is a multi-part message in MIME format.
>> --050404030901030607030308
>> Content-Type: text/plain; charset=UTF-8; format=flowed
>> Content-Transfer-Encoding: 7bit
>>
>> What about using
>>
>> WHERE f BETWEEN '201
Hi. I'm looking for an Open Source PHP code that will take plain text SQL
and turn it into colorful HTML. If it could take messy code and clean up
indents and such (a la SQLinForm), that would be a nice bonus. Ideally it
would understand many flavors of SQL, but handling Postgresql syntax is
mos
On Apr 25, 2012, at 6:57 AM, Ken Tanzer wrote:
> Hi. I'm looking for an Open Source PHP code that will take plain text SQL
> and turn it into colorful HTML. If it could take messy code and clean up
> indents and such (a la SQLinForm), that would be a nice bonus. Ideally it
> would understand
When i execute a sql script trough psql, it shows me errors in the
console window but it dosent write this errors in a log file.
psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql
>> C:/test_files/pg.log
psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sq
the windows user that owns the process ("postgres" by default) needs to
have the right to write in the folder to write a server log.
by default, this user has very few privileges (for good reasons - security).
about psql not writing that log:
>> means to redirect "standard out" to a file. But "st
like so:
http://support.microsoft.com/kb/110930
On Wed, Apr 25, 2012 at 2:27 PM, Willy-Bas Loos wrote:
> the windows user that owns the process ("postgres" by default) needs to
> have the right to write in the folder to write a server log.
> by default, this user has very few privileges (for go
Is anyone aware of a quick solution for producing user/db access reports
from pgsql syslog format logs?
in other words, I have a bunch of lines such as:
> Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1]
> user=database1_remote,db=sqm_remote_database1 LOG: connection authorized:
> user=dat
On 04/25/2012 06:36 AM, Larry J Prikockis wrote:
Is anyone aware of a quick solution for producing user/db access reports
from pgsql syslog format logs?
in other words, I have a bunch of lines such as:
Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1]
user=database1_remote,db=sqm_remote_da
We have a few daemon process that constantly pull batches of logs from a work
queue and then insert into or update a single table in a single transaction,
~1k rows at a time. I've been told the transaction does nothing other than
insert and update on that table, and I can verify the table in que
Ben Chobot writes:
> We have a few daemon process that constantly pull batches of logs from a work
> queue and then insert into or update a single table in a single transaction,
> ~1k rows at a time. I've been told the transaction does nothing other than
> insert and update on that table, and I
- Original Message -
From: Adrian Klaver
On 03/15/2012 09:17 AM, George Weaver wrote:
Hi All,
I am trying to use COPY to import postgresql logs into a postgresql
database for further review and sorting.
Are you using the CSV format to log to the Postgres log?:
http://www.postgres
On Apr 25, 2012, at 12:35 PM, Tom Lane wrote:
> Ben Chobot writes:
>> We have a few daemon process that constantly pull batches of logs from a
>> work queue and then insert into or update a single table in a single
>> transaction, ~1k rows at a time. I've been told the transaction does nothing
Ben Chobot writes:
> So, if I understand what you're saying, if I have two connections each
> transactionally updating many rows, then each transaction will need to
> acquire a RowExclusiveLock for each row (as documented), and also (as not
> documented?) each acquisition will temporarily acqui
On Wed, Apr 25, 2012 at 01:12:37PM -0600, Ben Chobot wrote:
> So, if I understand what you're saying, if I have two connections
> each transactionally updating many rows, then each transaction will
> need to acquire a RowExclusiveLock for each row (as documented), and
> also (as not documented?) ea
On Apr 25, 2012, at 1:31 PM, Tom Lane wrote:
> I don't have all the details in my head, but if you deliberately provoke
> a deadlock by making two transactions update the same two rows in
> opposite orders, you'll soon find out what it looks like in the log.
Heh, duh. Looks like your first guess
Martijn van Oosterhout writes:
> I think what you're missing here is that RowExclusiveLocks are taken by
> marking the row itself.
More specifically: row-level locks are not reflected in pg_locks at all.
A RowExclusiveLock entry in pg_locks reflects a *table* level lock,
which is taken by any INS
On Wed, 2012-04-25 at 10:40 +0200, Willy-Bas Loos wrote:
> On Wed, Apr 25, 2012 at 9:51 AM, Magnus Hagander wrote:
>
> > We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as
> > well, IIRC, specifically on Win32. Maybe you were hit by that one..
>
> Yes, possibly. I didn't even k
The table has a column 'coll_time' of type time without time zone. New
rows for the table are in a .sql file and the time values throw an error at
the colon between hours:minutes. Do time values need to be quoted?
TIA,
Rich
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
On Apr 25, 2012, at 4:01 PM, Rich Shepard wrote:
> The table has a column 'coll_time' of type time without time zone. New
> rows for the table are in a .sql file and the time values throw an error at
> the colon between hours:minutes. Do time values need to be quoted?
Yes, (date)time values need
On Wed, 25 Apr 2012, Ben Chobot wrote:
Yes, (date)time values need to be quoted as if they were strings.
Thanks, Ben. I thought that was the case but wanted to confirm it.
Much appreciated,
Rich
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
Hi,
PostgreSQL 9.0.4
I have this in pg_dumpall -g output (non-empty role names changed):
GRANT "" TO a GRANTED BY postgres;
GRANT "" TO b GRANTED BY c;
GRANT "" TO b GRANTED BY c;
GRANT "" TO b GRANTED BY c;
GRANT "" TO b GRANTED BY c;
GRANT "" TO "" GRANTED BY c;
GRANT "" TO "" GRANTED BY post
Hi there!
Today I realised that my knowledge concerning how postgres handles concurrency
is not very good, and its even worse when it comes to using that knowledge in
real-life.
Let me give you an example.
I have this table
create table userpositions ( userID int, positionID int, unique
(us
On Apr 25, 2012, at 5:17 PM, Janne H wrote:
> Hi there!
>
> Today I realised that my knowledge concerning how postgres handles
> concurrency is not very good, and its even worse when it comes to using that
> knowledge in real-life.
>
> Let me give you an example.
> I have this table
>
> crea
Hi,
Just wondering if anyone else has thoughts on this?
I'm still suspicious that this is a bug.
If I run EXPLAIN (or the query itself) on a database that has all the
schemas and tables created, but just the relevant data touched by the
query loaded.. then everything is fine.
The query plan
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Ben Chobot
> Sent: Wednesday, April 25, 2012 7:29 PM
> To: Janne H
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How can I see if my code is "concurrency
Bartosz Dmytrak writes:
> [ EXPLAIN VERBOSE fails for ]
> WITH t as (
> INSERT INTO "tblD1" (id, "Data1")
> VALUES ('a', 123)
> RETURNING *)
> UPDATE "tblBase"
> SET "SomeData" = 123
> WHERE id = 'a';
I've applied a patch for this. Thanks for the report!
regards, tom la
On Wed, Apr 25, 2012 at 4:17 PM, Janne H wrote:
> Hi there!
>
> Today I realised that my knowledge concerning how postgres handles
> concurrency is not very good, and its even worse when it comes to using that
> knowledge in real-life.
I think what everyone here is trying to say is that while P
On Wed, 25 Apr 2012 16:17:53 -0700 (PDT) Janne H wrote:
> Hi there!
>
> Today I realised that my knowledge concerning how postgres handles
> concurrency is not very good, and its even worse when it comes to using that
> knowledge in real-life.
>
> Let me give you an example.
> I have this tab
Toby Corkindale writes:
> Just wondering if anyone else has thoughts on this?
> I'm still suspicious that this is a bug.
Well, if you were to provide a reproducible test case, somebody might be
motivated to look into it. There could be a memory leak in the planner
somewhere, but without a test
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes:
> PostgreSQL 9.0.4
> I have this in pg_dumpall -g output (non-empty role names changed):
> GRANT "" TO a GRANTED BY postgres;
> GRANT "" TO b GRANTED BY c;
> GRANT "" TO b GRANTED BY c;
> GRANT "" TO b GRANTED BY c;
> GRANT "" TO b GRANTED BY c;
> GR
On 26/04/12 13:11, Tom Lane wrote:
Toby Corkindale writes:
Just wondering if anyone else has thoughts on this?
I'm still suspicious that this is a bug.
Well, if you were to provide a reproducible test case, somebody might be
motivated to look into it. There could be a memory leak in the p
Toby Corkindale writes:
> On 26/04/12 13:11, Tom Lane wrote:
>> Well, if you were to provide a reproducible test case, somebody might be
>> motivated to look into it. There could be a memory leak in the planner
>> somewhere, but without a test case it's not very practical to go look
>> for it.
>
On 26/04/12 15:30, Tom Lane wrote:
Toby Corkindale writes:
On 26/04/12 13:11, Tom Lane wrote:
Well, if you were to provide a reproducible test case, somebody might be
motivated to look into it. There could be a memory leak in the planner
somewhere, but without a test case it's not very practi
Toby Corkindale writes:
> On 26/04/12 15:30, Tom Lane wrote:
>> Hm, is the update target an inheritance tree?
> The target is the parent table of a bunch of partitions.
How many would "a bunch" be, exactly? I'm fairly sure that the complex
view would get re-planned for each target table ...
48 matches
Mail list logo
