Re: [GENERAL] Trigger Performance

Before reading. This is solved. Was an error on my part. On Sun, 2011-01-16 at 03:46 +, Jasen Betts wrote: > In plpgsql IF is an implicit select. > > > IF EXISTS (SELECT 1 FROM t1 WHERE > volume_id

Re: [GENERAL] Trigger Performance

On 2011-01-15, Randall Smith wrote: > Hi, > > I've created a trigger that checks the uniqueness of two columns in a > table. Traditionally, one would use a unique constraint, but in my > case, the size of the unique index would be too large and some > performance loss is acceptable. However, the

Re: [GENERAL] How to generate unique invoice numbers for each day

This is an very common and well understood problem. Take a look at this: http://www.varlena.com/GeneralBits/130.php -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-g

Re: [GENERAL] How to generate unique invoice numbers for each day

On 2011-01-15, Andrus Moor wrote: > Invoice numbers have format yymmddn > > where n is sequence number in day staring at 1 for every day. > > command > > SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), > '^[0-9]*'),'')::int),0)+1 > FROM invoice > where date= ?invoicedate > > is

Re: [GENERAL] Trigger Performance

On 15 Jan 2011, at 23:52, Randall Smith wrote: > Hi, > > I've created a trigger that checks the uniqueness of two columns in a > table. Traditionally, one would use a unique constraint, but in my > case, the size of the unique index would be too large and some > performance loss is acceptable.

Re: [GENERAL] Record with a field consisting of table rows

On 15 Jan 2011, at 21:57, Jon Smark wrote: >> No, of course not. A function cannot return different >> amounts of different return-values in any language I know >> of. > > Come on, you make it sound like it's something inconceivable or exotic, > even though pretty much any strongly-typed language

[GENERAL] libpq: multiple commands within single query

When I call PQsendQuery(..., "SELECT column1 FROM my_table; SELECT column2 FROM my_table; SELECT column3 FROM my_table") PQgetResult(...) successfully returns three results each containing 1 row and 1 column - that is what I need. But when I call something like PQsendQuery(..., "SELECT column1 FROM

[GENERAL] Trigger Performance

Hi, I've created a trigger that checks the uniqueness of two columns in a table. Traditionally, one would use a unique constraint, but in my case, the size of the unique index would be too large and some performance loss is acceptable. However, the trigger performance seems to be far below what'

[GENERAL] resizing a varchar column on 8.3.8

Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates

Re: [GENERAL] Record with a field consisting of table rows

Hi, > No, of course not. A function cannot return different > amounts of different return-values in any language I know > of. Come on, you make it sound like it's something inconceivable or exotic, even though pretty much any strongly-typed language with a post-1970s type-system will allow a func

Re: [GENERAL] How to generate unique invoice numbers foreach day

On Sat, 15 Jan 2011, Andrus Moor wrote: There are 365 days in year. Do you really think pre-creating sequence for every day for every year is best solution ? Andrus, I just saw this thread so my idea may not work for you. What I'd do is use the Julian date (that is, the sequential day from

Re: [GENERAL] Need help writing exclusion constraint

Dne 15.1.2011 21:07, Daniel Popowich napsal(a): > CREATE OR REPLACE FUNCTION overlap_at_dest(dest integer, > s timestamp, > e timestamp) > returns boolean as $_$

Re: [GENERAL] How to generate unique invoice numbers foreach day

In that case you have to manage the IDs on your own, the sequences won't help you in this (unless you really create one sequence for each day, which does not seem like a good solution to me). A really simple solution might be to do a BEFORE INSERT trigger that checks the last ID inserted for the d

Re: [GENERAL] Need help writing exclusion constraint

Matthew Wilson writes: > I have a table like this: > > create table event( > > destination_id integer not null references destination > (destination_id), > > starts timestamp, > ends timestamp > ); > > I want to make sure that no two rows **with the same destination_id** > over

Re: [GENERAL] How to generate unique invoice numbers foreach day

Invoices can entered also some days forward or back. Users enters invoice date and expected program to generate next sequential number for this day. Different users can enter invoices for different days. Andrus. - Original Message - From: Jorge Godoy To: Andrus Moor Cc: pgsql-

Re: [GENERAL] HA solution

On Saturday 15 January 2011 10:07:14 am Jaiswal Dhaval Sudhirkumar wrote: > Thanks for your support. > > We have power full HP servers with lots of CPU cores, I/O bandwidth and > memory too. > > Actually I will give you the environment details, which will help you to > understand. > > It is a huge

Re: [GENERAL] How to generate unique invoice numbers foreach day

If the gaps (user gets a number from a sequence and then rollbacks the transaction) are not a problem, then the sequences (reset every day) are probably the best solution. If the gaps are a problem (which is usually the case with invoicing systems), then you need to manage that on your own, e.g. u

Re: [GENERAL] How to generate unique invoice numbers foreach day

Why would you do that? You can always reset the sequence at the end of the day. -- Jorge Godoy On Sat, Jan 15, 2011 at 17:09, Andrus Moor wrote: > There are 365 days in year. > Do you really think pre-creating sequence for every day for every year is > best solution ? > > Andrus. > > --

[GENERAL] Need help writing exclusion constraint

I have a table like this: create table event( destination_id integer not null references destination (destination_id), starts timestamp, ends timestamp ); I want to make sure that no two rows **with the same destination_id** overlap in time. I'm not sure how to write this exclu

Re: [GENERAL] How to generate unique invoice numbers foreach day

There are 365 days in year. Do you really think pre-creating sequence for every day for every year is best solution ? Andrus. - Original Message - From: Jorge Godoy To: Andrus Moor Cc: pgsql-general@postgresql.org Sent: Saturday, January 15, 2011 8:41 PM Subject: ***SPAM**

Re: [GENERAL] How to generate unique invoice numbers for each day

Use a sequence. -- Jorge Godoy 2011/1/15 Andrus Moor > Invoice numbers have format yymmddn > > where n is sequence number in day staring at 1 for every day. > > command > > SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), > '^[0-9]*'),'')::int),0)+1 > FROM invoice > where

[GENERAL] How to generate unique invoice numbers for each day

Invoice numbers have format yymmddn where n is sequence number in day staring at 1 for every day. command SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1 FROM invoice where date= ?invoicedate is used to get next free invoice number if new invoice i

Re: [GENERAL] HA solution

Thanks for your support. We have power full HP servers with lots of CPU cores, I/O bandwidth and memory too. Actually I will give you the environment details, which will help you to understand. It is a huge set-up where we have a DC & DR. There will be lots of daily edit and read hits. Als

Re: [GENERAL] Record with a field consisting of table rows

On 15 Jan 2011, at 17:01, Jon Smark wrote: > Hi, > >> Nope, see my reply from yesterday around 20:23 >> You can return a table instead, with the count added as an >> extra column. > > I did see your solution, but note that it does not return a tuple > consisting of an integer and a setof (as I w

Re: [GENERAL] Record with a field consisting of table rows

Hi, > Nope, see my reply from yesterday around 20:23 > You can return a table instead, with the count added as an > extra column. I did see your solution, but note that it does not return a tuple consisting of an integer and a setof (as I wanted), but instead returns a setof of a tuple. I still

Re: [GENERAL] Install PostgreSQL as part of a desktop application, but how to coop with existing installations?

Bundling it as part of my application is even better. I didn't knew if that would be possible, but it would solve some of the issues. Oh, I meant to mention: Whether bundling directly in your installer or invoking the exe installer silently, you need to consider the major version incompatib

Re: [GENERAL] Install PostgreSQL as part of a desktop application, but how to coop with existing installations?

On 01/14/2011 03:45 PM, Jensen Somers wrote: Bundling it as part of my application is even better. I didn't knew if that would be possible, but it would solve some of the issues. Mainly data protection. The data that needs to be stored should not be altered by users. If they have access to the da

Re: [GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

2011/1/16 pasman pasmański : > I think this is a planner's bug. Can you send these explains to pgsql-bugs ? > Sure. BTW I thought I would change the query a little by putting a AND index_value .>100 instead of index_delta and it didn't help at all. I thought maybe using another index would help

Re: [GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

I think this is a planner's bug. Can you send these explains to pgsql-bugs ? On 1/15/11, Tim Uckun wrote: > 2011/1/15 pasman pasmański : >> Try : >> order by index_delta+1 desc >> > > I have attached the explain analyze for that below why does this > return instantly? > > > > > Limit (cost=29910

Re: [GENERAL] Time Series on Postgres (HOWTO?)

On 15 Jan 2011, at 1:41, bubba postgres wrote: > I've been googling, but haven't found a good answer to what I should do if I > want to store time series in Postgres. > My current solution is store serialized (compressed) blobs of data. > (So for example store 1 day worth of 1 minute samples (~14

Re: [GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

2011/1/15 pasman pasmański : > Try : > order by index_delta+1 desc > I have attached the explain analyze for that below why does this return instantly? Limit (cost=29910.05..29910.07 rows=10 width=1880) (actual time=42.563..42.563 rows=0 loops=1) -> Sort (cost=29910.05..29916.65 rows=264

Re: [GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

Try : order by index_delta+1 desc On 1/15/11, Tim Uckun wrote: > I have this query it runs reasonably quickly. > > > SELECT "consolidated_urls".* FROM "consolidated_urls" INNER JOIN > "topical_urls" > ON "consolidated_urls".id = "topical_urls".consolidated_url_id > WHERE (("topical_urls".domain