[SQL] Re: [NOVICE] Install two different versions of postgres which should run in parallel
On Fri, 2007-08-10 at 14:33 +0300, Loredana Curugiu wrote: > Hi all, > > I need to have two different vesions of postgres running in parallel > on > different ports. Does anyone knows how to install two different > versions > of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux > operating system. If you run Debian or Ubuntu you can do this with the package system. apt-get install postgresql-8.2 postgresql-7.4 The two versions are automatically installed to run on different ports. Look at the docs and manpages for the postgresql-common package to see how it works. If you can't do that, you can install a different version from source in a different directory tree; then have its postmaster run on a different port. Specify the port number when connecting so as to get to the postmaster you want. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Install two different versions of postgres which should run in parallel
Loredana Curugiu wrote: Hi all, I need to have two different vesions of postgres running in parallel on different ports. Does anyone knows how to install two different versions of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux operating system. Any information would greatly be appreciated. Loredana Shure. You just have to take care about --prefix (executable path) and --with-pgport (default port) when you ./configure, and also take care of the datadir you chose when executing initdb. Good luck. Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Index usage in order by with multiple columns in order-by-clause
I have the following test-case: CREATE TABLE test( name varchar PRIMARY KEY, value varchar NOT NULL, created timestamp not null ); create index test_lowernamevalue_idx ON test ((lower(name) || lower(value))); create index test_lowernamevaluecreated_idx ON test ((lower(name) || lower(value)), created); andreak=# EXPLAIN ANALYZE select * from test order by lower(name) || lower(value) ASC, created ASC; QUERY PLAN - Index Scan using test_lowernamevaluecreated_idx on test (cost=0.00..61.58 rows=770 width=72) (actual time=0.013..0.013 rows=0 loops=1) Total runtime: 0.127 ms (2 rows) andreak=# EXPLAIN ANALYZE select * from test order by lower(name) || lower(value) ASC, created DESC; QUERY PLAN Sort (cost=60.39..62.32 rows=770 width=72) (actual time=0.034..0.034 rows=0 loops=1) Sort Key: (lower((name)::text) || lower((value)::text)), created -> Seq Scan on test (cost=0.00..23.47 rows=770 width=72) (actual time=0.004..0.004 rows=0 loops=1) Total runtime: 0.123 ms (4 rows) As the EXPLAIN-output shows, the index is not used when sort-ordering differs in the two order-by-columns. Is there a way I can have multiple columns in the ORDER BY clause, each with different ASC/DESC-order and still use an index to speed up sorting? In my application I often have a need to sort by more than 3 columns, so I'm really wondering if there is a way to make sorting of multiple columsn (each which may have different sort-order) use an index? Preferrably without having to create 2^N indexes. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Install two different versions of postgres which should run in parallel
On 8/10/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote: > Hi all, > > I need to have two different vesions of postgres running in parallel on > different ports. Does anyone knows how to install two different versions > of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux > operating system. FYI, Linux isn't an OS, it's a kernel. With RedHat, you'll either need to build your own RPMs from source where each goes to a different directory etc OR compile from source using a different --prefix and then use separate startup scripts that set LD_LIBRARY_PATH and PATH etc... for each install on startup. If you're running Debian, you can install >1 version of pgsql side by side. Not sure how it's done exactly, but I've heard it repeated enough to believe it's true. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Best Fit SQL query statement
Hi Depesz,
I was curious about your solution for Best Fit since I had mine working in a
function with a loop:
...
FOR v_len IN REVERSE v_max..v_min LOOP
v_prefix := substring(v_destino, 1, v_len);
SELECT * INTO v_result
FROM numeracion
WHERE prefijo = v_prefix;
IF FOUND THEN
RETURN :v_result;
END IF;
END LOOP;
...
Found your query is shorter and clearer, problem is I couldn't have it use
an index. Thought it was a locale issue but adding a 2nd index with
varchar_pattern_ops made no difference.
In result, it turned out to be too slow in comparison to the function. Am I
missing something?
--- DDL ---
rd=# show lc_collate;
lc_collate
-
en_US.UTF-8
(1 row)
rd=# show client_encoding;
client_encoding
-
SQL_ASCII
(1 row)
rd=# show server_encoding;
server_encoding
-
SQL_ASCII
(1 row)
rd=# \d numeracion
Table "public.numeracion"
Column|Type | Modifiers
-+-+---
cod_oper| integer |
servicio| text| not null
modalidad | text| not null
localidad | text| not null
indicativo | text| not null
bloque | text| not null
resolucion | text|
fecha | date| not null
prefijo | text| not null
largo | integer |
fecha_carga | timestamp without time zone | default now()
Indexes:
"pk_numeracion" PRIMARY KEY, btree (prefijo)
"idx_numeracion_prefijo" btree (prefijo varchar_pattern_ops)
Foreign-key constraints:
"fk_numeracion_operadores_cod_oper" FOREIGN KEY (cod_oper) REFERENCES
operadores(cod_oper)
rd=# set enable_seqscan = off;
SET
rd=# explain select prefijo
rd-# FROM numeracion
rd-# WHERE '3514269565' LIKE prefijo || '%'
rd-# ORDER BY LENGTH(prefijo) DESC
rd-# LIMIT 1;
QUERY PLAN
Limit (cost=11077.54..11077.54 rows=1 width=89)
-> Sort (cost=11077.54..11077.91 rows=151 width=89)
Sort Key: length(prefijo)
-> Seq Scan on numeracion (cost=1.00..11072.07
rows=151 width=89)
Filter: ('3514269565'::text ~~ (prefijo || '%'::text))
Why I am getting these monstrous costs? Table had been vacuumed full just
before running the explain plan. It has ~31k rows.
Any hindsight will be greatly appreciated.
Regards,
Fernando.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Best Fit SQL query statement
On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote:
> Could anyone help me in writing Best Fit SQL statement.
> Suppose we have table t1 with coloumn t1 (text) with following rows.
> 98456
> 98457
> 9845
> 9846
> 984
> 985
> 98
> 99
> and if I query on 98456 the result must be 98456,
> However if I query on 98455 the result must be 9845
> and If I query 9849 the result must be 984
select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1)
desc limit 1;
should be ok.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Install two different versions of postgres which should run in parallel
Hi all, I need to have two different vesions of postgres running in parallel on different ports. Does anyone knows how to install two different versions of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux operating system. Any information would greatly be appreciated. Loredana
[SQL] Join optimization
Hi!
I've been trying to optimize a query in which I join several tables, since
I've seen it takes about 2 seconds, which is way too much.
Well, the query is the following, I'm using LEFT OUTER JOIN just when the
tables can have NULL results, plain JOIN otherwise:
select ="select to_char(a.fecha_publicacion,'dd/MM/')," +
"c.nombre,date_part('year',CURRENT_TIMESTAMP)-b.ano as
edad," +
"b.alzada,d.nombre,e.nombre,a.precio_a_mostrar,f.nombre," +
"a.destacado,a.visto,h.nombre,m.nombre,g.idprovincia," +
"g.provincia,b.raza_id,b.raza,b.disciplina_id" +
",b.disciplina " +
" from anuncio a JOIN caballo b " +
"ON (a.producto_id=b.id) " +
"JOIN raza c ON (b.raza_id=c.id) " +
" LEFT OUTER JOIN disciplina d " +
"ON (b.disciplina_id=d.id) " +
"LEFT OUTER JOIN nivel_disciplina e " +
"ON (b.disciplina_id=e.disciplina_id " +
"and b.nivel_id=e.nivel) " +
" JOIN anunciante_datos g ON (a.id_anunciante_datos = g.id)"
+
"JOIN provincia f ON (g.idprovincia=f.id) " +
"JOIN categoria h ON (a.categoria_id=h.id) " +
" LEFT OUTER JOIN sexo_caballo m ON " +
"(b.sexo_id=m.id) "+
"WHERE a.id=?";
I'd thought I could pull a trick on the fact that even though this "anuncio"
table (a) is relatively big (a few thousand entries), I really just need to
retrieve one result from it and combine it with the other tables. This
result would be that which matches with the ? in the a.id condition, which
is the Primary Key of this "anuncio" table. The other data is derived from
this specific result in "anuncio" (which btw means "advertisment" in
spanish). For example, "caballo" means "horse" in spanish, and its data is
retrieved as a horse related to the advertisement that sells such a horse.
My idea was to try to cut as soon as possible the few thousands of registers
from the "anuncio" table so that it might make it less costly to make the
query. For instance I tried this, but with no visible results:
" from anuncio a JOIN caballo b " +
"ON (a.id=? AND a.producto_id=b.id) " +
Any ideas on how to critically optimize the query?
Thank you lots =)
Re: [SQL] Best Fit SQL query statement
On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote: > Could anyone help me in writing Best Fit SQL statement. > Suppose we have table t1 with coloumn t1 (text) with following rows. > 98456 > 98457 > 9845 > 9846 > 984 > 985 > 98 > 99 > and if I query on 98456 the result must be 98456, > However if I query on 98455 the result must be 9845 > and If I query 9849 the result must be 984 select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1) desc limit 1; should be ok. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Using function like where clause
On Mon, Aug 06, 2007 at 04:44:29PM -0300, Ranieri Mazili wrote: > 1) Can I use a function that will return a string in a where clause like > bellow? > 2) Can I use a function that will return a string to return the list of > columns that I want to show like below? not in sql. you can in pl/pgsql. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Index usage in order by with multiple columns in order-by-clause
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > Is there a way I can have multiple columns in the ORDER BY clause, each with > different ASC/DESC-order and still use an index to speed up sorting? A btree index isn't magic, it's just an ordered list of entries. So you can't just randomly flip the ordering of individual columns. For instance, the natural sort order of a 2-column index on (x,y) is like x y 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 If you scan this index forwards, you get the equivalent of ORDER BY x ASC, y ASC If you scan it backwards, you get the equivalent of ORDER BY x DESC, y DESC But there is no way to get the equivalent of x ASC, y DESC from a scan of this index, nor x DESC, y ASC. If you have a specific requirement for one of those combinations, what you can do is build an index in which one of the columns is "reverse sorted". For instance, if we reverse-sort y, the index ordering looks like x y 1 3 1 2 1 1 2 3 2 2 2 1 3 3 3 2 3 1 Now we can get ORDER BY x ASC, y DESC from a forwards indexscan, or ORDER BY x DESC, y ASC from a backwards scan. But there's no way to get ASC/ASC or DESC/DESC from this index. If you really need all four orderings to be available, you're stuck with maintaining two indexes. Reverse-sorted index columns are possible but not well supported in existing PG releases (you need a custom operator class, and the planner is not all that bright about using them). 8.3 will have full support. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Race condition in resetting a sequence
On 8/4/07, Lew <[EMAIL PROTECTED]> wrote:
> Steve Midgley writes:
> >> The code I provided to reset a primary key sequence is actually part of
> >> Ruby on Rails core library - actually they use something very similar
> >> to what I originally sent:
> ...
> >> SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT
> >> increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence}))
> >> FROM #{table}), false)
>
> Tom Lane wrote:
> > Ugh. That's completely unsafe/broken, unless they also use locking that
> > you didn't show.
> ...
> > It doesn't have a race condition "all by itself": it will do what it's
> > told. The problem with commands such as the above is that there's a
> > time window between calculating the max() and executing the setval(),
> > and that window is more than large enough to allow someone else to
> > insert a row that invalidates your max() computation. (Because of MVCC
> > snapshotting, the risk window is in fact as long as the entire
> > calculation of the max --- it's not just a few instructions as some
> > might naively think.)
> >
> > Now it is possible to make this brute-force approach safe: you can lock
> > the table against all other modifications until you've applied your own
> > changes. But you pay a high price in loss of concurrency if you do
> > that.
>
> All this trouble over semantically-significant ID columns seems to support the
> camp that excoriates use of artificial ID columns and autoincrementation
> altogether.
I don't that it does that at all. If properly implemented, sequences
provide a race free way to assign unique ids to a table that otherwise
would have no natural primary key. I think that having a religious
preference one way or the other means you might use a suboptimal
method in some cases. In the db I work on, we have artificial pks of
sequences, artificial pks made from a large list of pre-created ids (6
alphanum char locator codes, common in the travel industry) and still
other tables that have natural primary keys. Each has a reason for
being the way it is.
I can't see this saying natural pks, which are often open to
interpretation and changes over time are any less are any better than
artificial pks from a sequence at all. I can see it saying that the
people who developed RoR framework were NOT database specialists.
> The usual argument in their favor is that they speed up performance, but this
> epicyclic dance to accomodate FK references to autoincremented keys makes the
> case that there is also a performance penalty, and in the more critical
> performance area of code development and correctness than in the less critical
> search speed area.
Sorry, but that's only one of the two arguments. The other one is
that natural pks are often impossible due to the dataset not being
able to be unique because of the business constraints.
In an airline reservation system, you might start with last and first
name for a pk. Then add phone number. except some people don't wanna
give their phone numbers, so you use steet address, only to find out
that John Smith with no phone lives on 123 Acorn St in Portland while
another John Smith with no phone lives on 123 Acorn St in
Phillidelphia.
And what do you do when they call back about their ticket?
Seriously, the natural primary key method has as many problems as the
artificial one.
In the USAF, they used to use a system to keep track of your medical
records. The first letter of your last name, followed by the last
four of your SSN. So, Ken Johnson might have an SSN of 123-45-6789
While Patricia Jackson might have an SSN of 453-89-6789.
Imagine Ken's surprise when his doctor tells him he's pregnant. Or
Patricia's surprise to have a limb amputated when she went in for a
problem with heart murmers.
By the time you add enough fields together to get a reliable primary
key, you might have a multi-dimensional monster that kills
performance.
You can't just religiously say that one or the other is the only answer.
Finally the cost of software development is only a small part of the
overall cost of creating and deploying a piece of code. Last place I
worked had a transactional database developed really quickly by non-db
people that used isam tables (no, not mysql with myisam, just isam)
that corrupted records dozens of times a day and required a team of
support people to keep it running semi-smoothly and hours of downtime
each night to be cleaned up. The initial savings on development costs
were eaten up rather quickly by the ongoing support costs.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] Re: [NOVICE] Install two different versions of postgres which should run in parallel
Loredana Curugiu wrote: > Hi all, > > I need to have two different vesions of postgres running in parallel on > different ports. Does anyone knows how to install two different versions > of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux > operating system. You can install from source and provide the --prefix argument to the configure command. Sean ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] foreign key pointing to diff schema?
Hi all. Can i make a FK who points a table in a different schema? Or this is implemented via a trigger by my own? Thanks! Gerardo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] foreign key pointing to diff schema?
On 8/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi all. Can i make a FK who points a table in a different schema? Or this > is implemented via a trigger by my own? Sure. just prefix the table name with the schemaname and a . create schema abc; alter user me set search_path='abc', 'public'; create table z1 (id int primary key); \d z1 Table "abc.z1" Column | Type | Modifiers +-+--- id | integer | not null Indexes: "z1_pkey" PRIMARY KEY, btree (id) (Note the abc.z1 there) create schema test3; create table test3.z2 (id int primary key, z1id int references abc.z1(id)); \d test3.z2 Table "test3.z2" Column | Type | Modifiers +-+--- id | integer | not null z1id | integer | Indexes: "z2_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "z2_z1id_fkey" FOREIGN KEY (z1id) REFERENCES z1(id) basically, schemas are just distinct name spaces. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] foreign key pointing to diff schema?
> On 8/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> Hi all. Can i make a FK who points a table in a different schema? Or >> this >> is implemented via a trigger by my own? > > Sure. just prefix the table name with the schemaname and a . > > create schema abc; > alter user me set search_path='abc', 'public'; > create table z1 (id int primary key); > \d z1 > Table "abc.z1" > Column | Type | Modifiers > +-+--- > id | integer | not null > Indexes: > "z1_pkey" PRIMARY KEY, btree (id) > > (Note the abc.z1 there) > create schema test3; > create table test3.z2 (id int primary key, z1id int references > abc.z1(id)); > \d test3.z2 >Table "test3.z2" > Column | Type | Modifiers > +-+--- > id | integer | not null > z1id | integer | > Indexes: > "z2_pkey" PRIMARY KEY, btree (id) > Foreign-key constraints: > "z2_z1id_fkey" FOREIGN KEY (z1id) REFERENCES z1(id) > > > basically, schemas are just distinct name spaces. > Im sory, that was just a matter of trying and see, isnt? I guess i need a beer :) Thanks for the example and the explanation! Gerardo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Best Fit SQL query statement
On Fri, Aug 10, 2007 at 04:40:34PM -0300, Fernando Hevia wrote:
> Found your query is shorter and clearer, problem is I couldn't have it use
> an index. Thought it was a locale issue but adding a 2nd index with
> varchar_pattern_ops made no difference.
> In result, it turned out to be too slow in comparison to the function. Am I
> missing something?
> rd=# explain select prefijo
> rd-# FROM numeracion
> rd-# WHERE '3514269565' LIKE prefijo || '%'
> rd-# ORDER BY LENGTH(prefijo) DESC
> rd-# LIMIT 1;
unfortunatelly this query will be hard to optimize.
i guess that functional approach will be the fastest, but you can try
with something like this:
select prefijo
from numeracion
where prefijo in (
select substr('3514269565',1,i)
from generate_series(1, length('3514269565')) i
)
order by length(prefijo) desc LIMIT 1;
it should be faster then the previous approach, but it will most
probably not be as fast as function.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Best Fit SQL query statement
On 8/10/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > unfortunatelly this query will be hard to optimize. Uh, how about SELECT MAX(t1) FROM t1 WHERE '9849' LIKE t1 || '%'; ---(end of broadcast)--- TIP 6: explain analyze is your friend
