Re: [GENERAL] PostgreSQL Live CD for 9.2.3 released

[Thomas Kellerer]

Devrim GÜNDÜZ, 21.02.2013 11:48:

Please let me know if you have any questions regarding this live CD.


Thanks for the work!
 
I have booted a VirtualBox system from the live CD.


Can I convert this to a "real" system?

The homepage mentions "Option to install image to hard drive" but I can't find 
anything on how to do this.

(Note: I'm not very experienced with Linux so I wouldn't be surprised if I'm 
missing something very obvious).

Thanks
Thomas




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What could cause a temp table to disappear?

[François Beausoleil]

Hi all,

I open a transaction, create a few temporary tables, import data in them, then 
insert into the final tables from the temporary tables and commit. In dev, it 
works, but not in prod. I'm trying to track down the cause. An outline of the 
generated SQL is (actual log at bottom):

BEGIN;
CREATE TEMPORARY TABLE shows_import( LIKE shows INCLUDING ALL ) ON COMMIT DROP;
INSERT INTO shows_import VALUES (...);

CREATE TEMPORARY TABLE airings_import( LIKE airings INCLUDING ALL ) ON COMMIT 
DROP;
INSERT INTO airings_import VALUES (...); -- 2500 rows
INSERT INTO airings_import VALUES (...); -- another 2500 rows

INSERT INTO shows SELECT * FROM shows_import;
INSERT INTO airings SELECT * FROM airings_import WHERE NOT EXISTS( SELECT * 
FROM airings WHERE ... );
COMMIT;

The error happens on the next to last step, with the following PostgreSQL error:

ERROR:  relation "airings_import" does not exist
LINE 3:   FROM airings_import
   ^: INSERT INTO airings
  SELECT *
  FROM airings_import
  WHERE NOT EXISTS(
  SELECT *
  FROM airings
  WHERE airings_import.show_id= airings.show_id
AND airings_import.channel_id = airings.channel_id
AND airings_import.start_at   = airings.start_at)

It surely is a case of me not the missing comma... I'm really flabbergasted by 
this.

As I said, dev works, not prod.

Dev version is:
 PostgreSQL 9.1.3 on x86_64-apple-darwin10.8.0, compiled by 
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit

Production version is:
 PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 
4.7.2, 64-bit

Any hints?
François


NOTE: Log cut at 120 characters wide for easier viewing.

[INFO ] binding-data-persister:14072 - (0.001154s) BEGIN
[INFO ] binding-data-persister:14072 - (0.014419s) CREATE TEMPORARY TABLE 
markets_import( LIKE markets INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.006675s) INSERT INTO "markets_import" 
("market_id", "name", "geo_re", "short_name", "latitude", "longitude", "la
[INFO ] binding-data-persister:14072 - (0.004407s) CREATE TEMPORARY TABLE 
channels_import( LIKE channels INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.020669s) INSERT INTO 
"channels_import" ("channel_id", "name", "language", "cable") VALUES 
('3056df00-90b7-012f-6
[INFO ] binding-data-persister:14072 - (0.002994s) CREATE TEMPORARY TABLE 
channel_market_memberships_import( LIKE channel_market_memberships INCLUDING ALL
[INFO ] binding-data-persister:14072 - (0.006635s) INSERT INTO 
"channel_market_memberships_import" ("market_id", "channel_id") VALUES 
('2ec7d8f0-e5f6-012e
[INFO ] binding-data-persister:14072 - (0.009247s) CREATE TEMPORARY TABLE 
shows_import( LIKE shows INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.155186s) INSERT INTO "shows_import" 
("show_id", "name", "hashtag", "number_of_keywords", "number_of_hashtags", "
[INFO ] binding-data-persister:14072 - (0.013038s) INSERT INTO "shows_import" 
("show_id", "name", "hashtag", "number_of_keywords", "number_of_hashtags", "
[INFO ] binding-data-persister:14072 - (0.005852s) CREATE TEMPORARY TABLE 
episodes_import( LIKE episodes INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.053025s) INSERT INTO 
"episodes_import" ("episode_id", "show_id", "name") VALUES 
('a2dde110-d643-012e-eba1-40400f
...
[INFO ] binding-data-persister:14072 - (0.049428s) INSERT INTO 
"episodes_import" ("episode_id", "show_id", "name") VALUES 
('68318970-52d4-0130-b15d-7a163e
[INFO ] binding-data-persister:14072 - (0.005152s) CREATE TEMPORARY TABLE 
producers_import( LIKE producers INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.009256s) INSERT INTO 
"producers_import" ("producer_id", "name") VALUES 
('e91dc8f0-d385-012e-eb99-40400fe46aa7',
[INFO ] binding-data-persister:14072 - (0.006859s) CREATE TEMPORARY TABLE 
airings_import( LIKE airings INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.103249s) INSERT INTO "airings_import" 
("airing_id", "channel_id", "show_id", "start_at", "end_at") VALUES ('61ec
...
[INFO ] binding-data-persister:14072 - (0.089145s) INSERT INTO "airings_import" 
("airing_id", "channel_id", "show_id", "start_at", "end_at") VALUES ('03ea
[INFO ] binding-data-persister:14072 - (0.011618s) TRUNCATE TABLE "markets"
[INFO ] binding-data-persister:14072 - (0.006574s) INSERT INTO "markets" SELECT 
* FROM "markets_import" RETURNING "market_id"
[INFO ] binding-data-persister:14072 - (0.004189s) TRUNCATE TABLE "channels"
[INFO ] binding-data-persister:14072 - (0.008122s) INSERT INTO "channels" 
SELECT * FROM "channels_import" RETURNING "channel_id"
[INFO ] binding-data-persister:14072 - (0.003201s) TRUNCATE TABLE 
"channel_market_memberships"
[INFO ] binding-data-persister:14072 - (0.006054s) INSERT INTO 
"channel_market_memberships" SELECT * FROM "channel_market_memberships_import" 

Re: [GENERAL] What could cause a temp table to disappear?

[Adrian Klaver]

On 02/27/2013 01:19 PM, François Beausoleil wrote:

Hi all,

I open a transaction, create a few temporary tables, import data in them, then 
insert into the final tables from the temporary tables and commit. In dev, it 
works, but not in prod. I'm trying to track down the cause. An outline of the 
generated SQL is (actual log at bottom):

BEGIN;
CREATE TEMPORARY TABLE shows_import( LIKE shows INCLUDING ALL ) ON COMMIT DROP;
INSERT INTO shows_import VALUES (...);

CREATE TEMPORARY TABLE airings_import( LIKE airings INCLUDING ALL ) ON COMMIT 
DROP;
INSERT INTO airings_import VALUES (...); -- 2500 rows
INSERT INTO airings_import VALUES (...); -- another 2500 rows

INSERT INTO shows SELECT * FROM shows_import;
INSERT INTO airings SELECT * FROM airings_import WHERE NOT EXISTS( SELECT * 
FROM airings WHERE ... );
COMMIT;

The error happens on the next to last step, with the following PostgreSQL error:

ERROR:  relation "airings_import" does not exist
LINE 3:   FROM airings_import
^: INSERT INTO airings
   SELECT *
   FROM airings_import
   WHERE NOT EXISTS(
   SELECT *
   FROM airings
   WHERE airings_import.show_id= airings.show_id
 AND airings_import.channel_id = airings.channel_id
 AND airings_import.start_at   = airings.start_at)

It surely is a case of me not the missing comma... I'm really flabbergasted by 
this.





Any hints?


See in line notes below.
Also what client/library are you using to connect with?


François


NOTE: Log cut at 120 characters wide for easier viewing.

[INFO ] binding-data-persister:14072 - (0.006859s) CREATE TEMPORARY TABLE 
airings_import( LIKE airings INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.103249s) INSERT INTO "airings_import" ("airing_id", "channel_id", 
"show_id", "start_at", "end_at") VALUES ('61ec
...
[INFO ] binding-data-persister:14072 - (0.089145s) INSERT INTO "airings_import" ("airing_id", "channel_id", 
"show_id", "start_at", "end_at") VALUES ('03ea



So what is happening below and does that happen on dev?


[INFO ] binding-data-persister:14072 - (0.011618s) TRUNCATE TABLE "markets"
[INFO ] binding-data-persister:14072 - (0.006574s) INSERT INTO "markets" SELECT * FROM 
"markets_import" RETURNING "market_id"
[INFO ] binding-data-persister:14072 - (0.004189s) TRUNCATE TABLE "channels"
[INFO ] binding-data-persister:14072 - (0.008122s) INSERT INTO "channels" SELECT * FROM 
"channels_import" RETURNING "channel_id"
[INFO ] binding-data-persister:14072 - (0.003201s) TRUNCATE TABLE 
"channel_market_memberships"
[INFO ] binding-data-persister:14072 - (0.006054s) INSERT INTO "channel_market_memberships" 
SELECT * FROM "channel_market_memberships_import" RETURNING "m
[INFO ] binding-data-persister:14072 - (0.011205s) TRUNCATE TABLE "shows"
[INFO ] binding-data-persister:14072 - (0.063107s) INSERT INTO "shows" SELECT * FROM 
"shows_import" RETURNING "show_id"
[INFO ] binding-data-persister:14072 - (0.008198s) TRUNCATE TABLE "episodes"
[INFO ] binding-data-persister:14072 - (0.738297s) INSERT INTO "episodes" SELECT * FROM 
"episodes_import" RETURNING "episode_id"
[INFO ] binding-data-persister:14072 - (0.004846s) TRUNCATE TABLE "producers"
[INFO ] binding-data-persister:14072 - (0.005278s) INSERT INTO "producers" SELECT * FROM 
"producers_import" RETURNING "producer_id"
[ERROR] binding-data-persister:14072 - PG::Error: ERROR:  relation 
"airings_import" does not exist
[INFO ] binding-data-persister:14072 - (1.583302s) ROLLBACK




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What could cause a temp table to disappear?

[François Beausoleil]

Le 2013-02-27 à 16:45, Adrian Klaver a écrit :

> On 02/27/2013 01:19 PM, François Beausoleil wrote:
> 
>> 
>> Any hints?
> 
> See in line notes below.
> Also what client/library are you using to connect with?

I use Ruby & Sequel. Sequel is a very thin library over straight SQL.

>> NOTE: Log cut at 120 characters wide for easier viewing.
>> 
>> [INFO ] binding-data-persister:14072 - (0.006859s) CREATE TEMPORARY TABLE 
>> airings_import( LIKE airings INCLUDING ALL ) ON COMMIT DROP
>> [INFO ] binding-data-persister:14072 - (0.103249s) INSERT INTO 
>> "airings_import" ("airing_id", "channel_id", "show_id", "start_at", 
>> "end_at") VALUES ('61ec
>> ...
>> [INFO ] binding-data-persister:14072 - (0.089145s) INSERT INTO 
>> "airings_import" ("airing_id", "channel_id", "show_id", "start_at", 
>> "end_at") VALUES ('03ea
> 
> 
> So what is happening below and does that happen on dev?
> 
>> [INFO ] binding-data-persister:14072 - (0.011618s) TRUNCATE TABLE "markets"
>> [INFO ] binding-data-persister:14072 - (0.006574s) INSERT INTO "markets" 
>> SELECT * FROM "markets_import" RETURNING "market_id"
>> [INFO ] binding-data-persister:14072 - (0.004189s) TRUNCATE TABLE "channels"
>> [INFO ] binding-data-persister:14072 - (0.008122s) INSERT INTO "channels" 
>> SELECT * FROM "channels_import" RETURNING "channel_id"
>> [INFO ] binding-data-persister:14072 - (0.003201s) TRUNCATE TABLE 
>> "channel_market_memberships"
>> [INFO ] binding-data-persister:14072 - (0.006054s) INSERT INTO 
>> "channel_market_memberships" SELECT * FROM 
>> "channel_market_memberships_import" RETURNING "m
>> [INFO ] binding-data-persister:14072 - (0.011205s) TRUNCATE TABLE "shows"
>> [INFO ] binding-data-persister:14072 - (0.063107s) INSERT INTO "shows" 
>> SELECT * FROM "shows_import" RETURNING "show_id"
>> [INFO ] binding-data-persister:14072 - (0.008198s) TRUNCATE TABLE "episodes"
>> [INFO ] binding-data-persister:14072 - (0.738297s) INSERT INTO "episodes" 
>> SELECT * FROM "episodes_import" RETURNING "episode_id"
>> [INFO ] binding-data-persister:14072 - (0.004846s) TRUNCATE TABLE "producers"
>> [INFO ] binding-data-persister:14072 - (0.005278s) INSERT INTO "producers" 
>> SELECT * FROM "producers_import" RETURNING "producer_id"
>> [ERROR] binding-data-persister:14072 - PG::Error: ERROR:  relation 
>> "airings_import" does not exist
>> [INFO ] binding-data-persister:14072 - (1.583302s) ROLLBACK

This is the production log, and it's the exact same in dev. The data I'm 
putting in the tables is authoritative, except for airings, which may or may 
not already exist in the database. In dev, the INSERT INTO airings SELECT * 
FROM airings_import WHERE NOT EXISTS(...) statement runs to completion. That's 
why I'm puzzled.

Bye,
François

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] What could cause a temp table to disappear?

[Adrian Klaver]

On 02/27/2013 01:48 PM, François Beausoleil wrote:


Le 2013-02-27 à 16:45, Adrian Klaver a écrit :


On 02/27/2013 01:19 PM, François Beausoleil wrote:



Any hints?


See in line notes below.
Also what client/library are you using to connect with?


I use Ruby & Sequel. Sequel is a very thin library over straight SQL.





This is the production log, and it's the exact same in dev. The data I'm
putting in the tables is authoritative, except for airings, which may or
may not already exist in the database. In dev, the INSERT INTO airings
SELECT * FROM airings_import WHERE NOT EXISTS(...) statement runs to
completion. That's why I'm puzzled.


Everything else is the same? It is acting like something is closing a 
session on you. Could there be a connection pooler in the mix?




Bye,
François



--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What could cause a temp table to disappear?

[Tom Lane]

Adrian Klaver  writes:
> On 02/27/2013 01:48 PM, François Beausoleil wrote:
>> Le 2013-02-27 à 16:45, Adrian Klaver a écrit :
>>> Also what client/library are you using to connect with?

>> I use Ruby & Sequel. Sequel is a very thin library over straight SQL.

> Everything else is the same? It is acting like something is closing a 
> session on you. Could there be a connection pooler in the mix?

Or just closing a transaction --- note the ON COMMIT DROP.  I'm thinking
something in the client-side stack is doing something you don't expect.
Suggest turning on server-side logging (log_statement = all, maybe
log_connections too) rather than trusting this client-side log to be
telling the whole truth.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What could cause a temp table to disappear?

[François Beausoleil]

Le 2013-02-27 à 16:59, Tom Lane a écrit :

> Adrian Klaver  writes:
>> On 02/27/2013 01:48 PM, François Beausoleil wrote:
>>> Le 2013-02-27 à 16:45, Adrian Klaver a écrit :
 Also what client/library are you using to connect with?
> 
>>> I use Ruby & Sequel. Sequel is a very thin library over straight SQL.
> 
>> Everything else is the same? It is acting like something is closing a 
>> session on you. Could there be a connection pooler in the mix?
> 
> Or just closing a transaction --- note the ON COMMIT DROP.  I'm thinking
> something in the client-side stack is doing something you don't expect.
> Suggest turning on server-side logging (log_statement = all, maybe
> log_connections too) rather than trusting this client-side log to be
> telling the whole truth.

Ok, as you suspected, there are multiple connections and the wrong connection 
was used to execute the wrong queries. I haven't fully found the root cause, 
but I know where to look.

Thanks for your help!
François

smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Poor performance when using a window function in a view

[Chris Hanks]

Hi. Sorry if this is repetitive, I tried posting to pgsql-performance first
but I think it got stuck in moderation.

I'm trying to create a view that uses a window function, but it seems that
Postgres is unable to optimize it. Here's a reproduction of my situation
with 9.2.2:

---

drop table if exists values cascade;

create table values (
  fkey1 integer not null,
  fkey2 integer not null,
  fkey3 integer not null,
  value float not null,
  constraint values_pkey primary key (fkey1, fkey2, fkey3)
);

-- This is kind of hacky, but it roughly resembles the size and
distribution of my dataset.
insert into values select distinct on (fkey1, fkey2, fkey3)
  i / 12 + 1 as fkey1,
  i % 4 + 1 as fkey2,
  ceil(random() * 10) as fkey3,
  random() * 2 - 1 as value from generate_series(0, 19) i;

create or replace view values_view as
select fkey1, fkey3,
  (derived1 / max(derived1) over (partition by fkey1)) as derived1,
  (derived2 / sum(derived1) over (partition by fkey1)) as derived2
from (
  select fkey1, fkey3,
cast(sum((case when (value > 0.0) then 4 else 1 end)) as double
precision) as derived1,
sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) as
derived2
  from values
  group by fkey1, fkey3
) as t1;

-- This query requires a sequential scan on values, though all the data it
needs could be found much more efficiently with an index scan.
explain analyze select * from values_view where fkey1 = 1263;

---

Can anyone suggest a way to rewrite this query? Or if postgres isn't
capable of optimizing this right now, is there a workaround of some kind?
This is a view I'd like to be able to join a smaller table against.

Thanks - Chris

Re: [GENERAL] Poor performance when using a window function in a view

[Merlin Moncure]

On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks
 wrote:
> Hi. Sorry if this is repetitive, I tried posting to pgsql-performance first
> but I think it got stuck in moderation.
>
> I'm trying to create a view that uses a window function, but it seems that
> Postgres is unable to optimize it. Here's a reproduction of my situation
> with 9.2.2:
>
> ---
>
> drop table if exists values cascade;
>
> create table values (
>   fkey1 integer not null,
>   fkey2 integer not null,
>   fkey3 integer not null,
>   value float not null,
>   constraint values_pkey primary key (fkey1, fkey2, fkey3)
> );
>
> -- This is kind of hacky, but it roughly resembles the size and distribution
> of my dataset.
> insert into values select distinct on (fkey1, fkey2, fkey3)
>   i / 12 + 1 as fkey1,
>   i % 4 + 1 as fkey2,
>   ceil(random() * 10) as fkey3,
>   random() * 2 - 1 as value from generate_series(0, 19) i;
>
> create or replace view values_view as
> select fkey1, fkey3,
>   (derived1 / max(derived1) over (partition by fkey1)) as derived1,
>   (derived2 / sum(derived1) over (partition by fkey1)) as derived2
> from (
>   select fkey1, fkey3,
> cast(sum((case when (value > 0.0) then 4 else 1 end)) as double
> precision) as derived1,
> sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) as
> derived2
>   from values
>   group by fkey1, fkey3
> ) as t1;
>
> -- This query requires a sequential scan on values, though all the data it
> needs could be found much more efficiently with an index scan.
> explain analyze select * from values_view where fkey1 = 1263;
>
> ---
>
> Can anyone suggest a way to rewrite this query? Or if postgres isn't capable
> of optimizing this right now, is there a workaround of some kind? This is a
> view I'd like to be able to join a smaller table against.

this comes up a lot. only way to expose as a view is to push the query
into a set returning function which you then wrap into a view.
downside is that any query except on fkey1/fkey 2 will have to fully
materialize view.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Similarity Search with Wildcards

[Ghislain Hachey]

Hi list,

I have a varchar column with content such as "Client Name - Brief
Description of Problem" (it's a help desk ticket system). I want to
generate reports by clients and the only thing I can base my query on is
this column. The client names often contain typos or are entered
slightly differently. I installed the pg_trgm extension and it almost
does what I want. The problem is that it searches the similarity of the
whole field and not just the client name resulting in not so similar
searches (I include my query below).

SELECT
  tickets.id as ticket_id, 
  tickets.subject as ticket_subject,
  similarity(tickets.subject, 'Client Name') AS sml,
FROM
 tickets
WHERE
  tickets.subject % 'Client Name';
 
I thought about using wildcards as discussed here

but this does not seem to have any effect (I include the query I tried
below).

SELECT
  tickets.id as ticket_id, 
  tickets.subject as ticket_subject,
  similarity(tickets.subject, '%Client Name%') AS sml,
FROM
 tickets
WHERE
  tickets.subject % '%Client Name%';

Both queries result in the same similarity. I would hope that the
similarity algorithm would only work on the "Client Name" part of the
string and ignore what is before and after; in other words, the latter
query above would return a similarity factor of 1 on the content "Client
Name - Brief Description of Problem".

Any pointer in a right direction would be appreciated.

-- 
GH

Re: [GENERAL] Similarity Search with Wildcards

[John R Pierce]

On 2/27/2013 10:35 PM, Ghislain Hachey wrote:
I have a varchar column with content such as "Client Name - Brief 
Description of Problem" (it's a help desk ticket system). I want to 
generate reports by clients and the only thing I can base my query on 
is this column. The client names often contain typos or are entered 
slightly differently. I installed the pg_trgm extension and it almost 
does what I want. The problem is that it searches the similarity of 
the whole field and not just the client name resulting in not so 
similar searches (I include my query below).



why isn't client name a separate field??   thats the logical approach



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general