Suresh Raja | 18 Apr 07:30 2015
Picon

function to send email with query results

Hi all:

I'm looking to write a function to send email with result of a query.    Is it possible to send email with in a function.  Any help is appreciated. 

Thanks,
-Suresh Raja 
Pai-Hung Chen | 18 Apr 04:53 2015
Picon

ORDER BY for jsonb

Hi,

I am new to PostgreSQL and have a question about the new jsonb type in 9.4. Suppose I have a table called "user" that has two columns: (1) "user_id" of type text, also the primary key, (2) "setting" of type jsonb. With the following query pattern:

SELECT *
FROM user
WHERE user_id IN [...]
ORDER BY setting->>'foo',
         setting->>'bar',
         ...

where ORDER BY clause can contain an arbitrary list of root-level fields in "setting". In this case, how should I create indexes for "user" to get good query performance? 

Thanks,
Pai-Hung
Jim Nasby | 18 Apr 02:20 2015

"Cast" SRF returning record to a table type?

I'm working on a function that will return a set of test data, for unit 
testing database stuff. It does a few things, but ultimately returns 
SETOF record that's essentially:

RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name;

Because it's always going to return a real relation, I'd like to be able 
to the equivalent of:

SELECT ... FROM my_function( 'some_table' )::some_table;

Is there any trick that would allow that to work? I know that instead of 
'SELECT * ...' I can do 'SELECT row(t.*) FROM ' || table_name || ' AS t' 
and then do

SELECT ... FROM my_function( 'some_table' ) AS data( d some_table )

but I'm hoping to avoid the extra level of indirection.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--

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

rob stone | 18 Apr 00:09 2015
Picon

Running pg_upgrade under Debian

Hello,

I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this
error:-

postgres <at> roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade
-b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin
-d /home/postgres/data93/userqueries
-D /home/postgres/data94/userqueries -U pguserqueries

check for "/home/postgres/data93/userqueries/base" failed: No such file
or directory

Failure, exiting
postgres <at> roblaptop:/usr/lib/postgresql/9.4/bin$

postgres <at> roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade --version
pg_upgrade (PostgreSQL) 9.4.1

I have two tablespaces defined for this data -- one to hold the tables
and another for the indices.

There is no "base" file or directory.

Do I just create a dummy directory named "base"?

If there is supposed to be a directory or file named "base" why wasn't
it created by initdb?

Regards,
Robert

--

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

Kynn Jones | 17 Apr 17:34 2015
Picon

On using doubles as primary keys

I have some data in the form of a matrix of doubles (~2 million
rows, ~400 columns) that I would like to store in a Pg table,
along with the associated table of metadata (same number of rows,
~30 columns, almost all text).  This is large enough to make
working with it from flat files unwieldy.

(The only reason for wanting to transfer this data to a Pg table
is the hope that it will be easier to work with it by using SQL
queries to extract subsets of it, than by, e.g., writing programs
that must scan the entire matrix every time they're run.)

My plan is to add one extra column to the Pg tables to serve as
the primary key joining the data and the metadata tables
together.

One consideration that is complication the choice of primary key
is wanting to have the ability to store chunks of the data
table (not the metadata table), including the PK column, as
matrices of doubles.  In its simplest form, this would mean using
doubles as primary keys, but this seems to me a bit weird.

I'm willing to go ahead with this, but I wanted to ask for your
feedback on the whole thing.  In particular I'd like to know if
there are there standard ways for using doubles as primary keys,
and, conversely, if there are known pitfalls I should be looking
out for, but I welcome any other words of wisdom you may have on
this topic.

Thanks in advance!

kj

--

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

Geoff Speicher | 17 Apr 00:21 2015

Re: fillfactor and cluster table vs ZFS copy-on-write

On Thu, Apr 16, 2015 at 4:56 PM, Qingqing Zhou <zhouqq.postgres <at> gmail.com> wrote:
On Thu, Apr 16, 2015 at 5:09 AM, Geoff Speicher <gspeicher <at> umtechx.com> wrote:
> Therefore one might posit that PostgreSQL should be configured to use 100%
> fillfactor and avoid clustering on ZFS. Can anyone comment on this?
>

Even with COW, I can see fillfactor < 100% still have its virtues. For
example, HOT update can avoid adding an extra index item on the index
page if it finds the new item can be inserted in the same heap page.

That's true, the new physical location on disk is transparent to the DBMS so it has no more or less housekeeping with or without COW, but the housekeeping still has to be done somewhere, so it helps to understand which is more efficient. I'll see if I can produce some empirical data unless anyone thinks it's a waste of time.

When you do CLUSTER command, engine will overwrite table into new
files any way, so COW does not affect here.

I was thinking about CLUSTER more in terms of its positive side-effects to the effective fillfactor but your point is taken.

Thanks,
Geoff
Adrian Klaver | 17 Apr 02:35 2015

Re: database migration question between different ubuntus and different postgresql server versions

On 04/16/2015 03:14 PM, Octavi Fors wrote:
> Hi Adrian,
>
> at first glance, option 1) seems to me simpler. But does it guarantee
> server version upgrade compatibility?
>
> Could you/someone please provide an example of commands which I could use?

See Andrews answer.

There is the matter of the tablespace.

It was not clear in your first post what you plan to do with the tablespace?

Are you going to keep it on the NAS or put it somewhere else?

>
> Thanks a lot,
>
> Octavi.
>
>

-- 
Adrian Klaver
adrian.klaver <at> aklaver.com

--

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

Andrew Sullivan | 17 Apr 00:22 2015
Picon

Re: database migration question between different ubuntus and different postgresql server versions

On Thu, Apr 16, 2015 at 06:14:20PM -0400, Octavi Fors wrote:
> at first glance, option 1) seems to me simpler. But does it guarantee
> server version upgrade compatibility?

Yes.  Use the pg_dump from the later postgres, which can read old
versions and generate any output needed for the new version.  It's
just like any other pg_dump otherwise.  

> Could you/someone please provide an example of commands which I could use?

Usually pg_dump [connection options] databasename | psql [connection
options] databasename

For instance, if you wanted from the new machine to dump egdb from the
old machine and restore locally, you could do

pg_dump -U postgres -h 192.0.2.1 -C egdb | psql -U postgres

I recommend reading the pg_dump (and if you like, pg_dumpall) manuals
before proceeding.

A

-- 
Andrew Sullivan
ajs <at> crankycanuck.ca

--

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

Octavi Fors | 16 Apr 23:01 2015
Picon

database migration question between different ubuntus and different postgresql server versions

Dear all,

I have one newbie question which I hope one kind soul of this list can help me.

The situation is that I have two postgresql servers:

  -9.2 running on Ubuntu 12.04 with a database 'db' already created and populated with data,

  -9.3 running on Ubuntu 14.04.02 with no database created (no tables, no data),

I want to migrate the database 'db' (tablespace+tables+data) from computer with 9.2 server to computer with 9.3 server.

Note: database 'db' is living on a NAS mounted via NFS, with a tablespace modification I was given in this list last week:

CREATE TABLESPACE onNAS LOCATION '/NAS-box/pgsql-data';
CREATE DATABASE db TABLESPACE onNAS;


I would appreciate if you could provide commands I can type to make me start with the process.

Thanks in advance,

Octavi Fors.
Andomar | 16 Apr 22:24 2015
Picon

Waiting on ExclusiveLock on extension

Hi,

After upgrading our database from 9.3.5 to 9.4.1 last night, the server 
suffers from high CPU spikes. During these spikes, there are a lot of 
these messages in the logs:

     process X still waiting for ExclusiveLock on extension of relation 
Y of database Z after 1036.234 ms

And:

     process X acquired ExclusiveLock on extension of relation Y of 
database Z after 2788.050 ms

What looks suspicious is that there are sometimes several "acquired" 
messages for the exact same relation number in the exact same millisecond.

a) I'm assuming "extension" means growing the physical diskspace used by 
a table-- is that correct?
b) How can you find the name of the relation being extended? based on 
the relation number.
c) Why would Postgres grow a table twice in the same millisecond? Could 
it be an index with a high fill factor?

Any suggestions on how to approach this issue are welcome.

Thanks for your time,
Andomar

P.S. The upgrade was done with pg_dump.  So the database was converted 
to SQL and then imported into 9.4.

--

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

William Dunn | 16 Apr 16:52 2015
Picon

PL\pgSQL 'ERROR: invalid input syntax for type oid:' [PostgreSQL 9.3.6 and 9.4]

Hello list,

I am creating a plpgsql procedure in Postgres 9.4 (also testing in 9.3.6) to move all of the tables that are not in a default tablespace (pg_default, pg_global, or 0) into the tablespace pg_default. However when it executes I get an error 'ERROR:  invalid input syntax for type oid:' which I do not know how to resolve..

The procedure executes the following select query, which returns the relname (tablename, type name) and nspname (schema name, type name) of each table that are not in the default tablespaces, into a variable called row_data (of type pg_catalog.pg_class%ROWTYPE):

   SELECT pg_class.relname, pg_namespace.nspname 
   FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid 
   WHERE pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE spcname='pg_default') 
   AND pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE spcname='pg_global') 
   AND pg_class.reltablespace<>0 
   AND pg_class.relkind='r' 
   ORDER BY pg_class.relname;

Using the example database EDBSTORE (example database provided by Enterprise DB) the query returned the table 'inventory' which was in schema 'edbstore' (which I had stored on tablespace 'edbstore', not pg_default):
  relname  | nspname  
-----------+----------
 inventory | edbstore
(1 row)


The procedure loops through each returned row and executes an ALTER TABLE command to move them to the tablespace pg_default:
EXECUTE 'ALTER TABLE '|| row_data.nspname || '.' || row_data.relname ||' SET TABLESPACE pg_default';

(so in the above edbstore example it should execute "ALTER TABLE edbstore.inventory SET TABLESPACE pg_default;")

However, when I run the procedure it is returning the following error:
   ERROR:  invalid input syntax for type oid: "edbstore"
   CONTEXT:  PL/pgSQL function move_table_tablespaces_to_pg_default() line 18 at FOR over SELECT rows

Does anyone understand this error?

The full plpgsql function is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE FUNCTION move_table_tablespaces_to_pg_default () RETURNS INTEGER AS $$
-- Loops through the tables not in the tablespace pg_default, pg_global, or the default tablespace and moves them to the pg_default tablespace
-- Returns the number of tables that were moved

   DECLARE

      -- Declare a variable to hold the counter of tables moved
      objects_affected INTEGER = 0;

      -- Declare a variable to hold rows from the pg_class table
      row_data pg_catalog.pg_class%ROWTYPE;

   BEGIN

      -- Iterate through the results of a query which lists all of the tables not in the tablespace pg_default, pg_global, or the default tablespace
      FOR row_data IN (SELECT pg_class.relname, pg_namespace.nspname FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid 

WHERE pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE spcname='pg_default') AND pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE 

spcname='pg_global') AND pg_class.reltablespace<>0 AND pg_class.relkind='r' ORDER BY pg_class.relname)  LOOP

         -- execute ALTER TABLE statement on that table to move it to tablespace pg_default
         EXECUTE 'ALTER TABLE '|| row_data.nspname || '.' || row_data.relname ||' SET TABLESPACE pg_default';

         -- increment count of tables moved
         objects_affected := objects_affected + 1;
      END LOOP;

      -- Return count of tables moved
      -- RETURN objects_affected;
   END;
$$ LANGUAGE 'plpgsql';

Thanks!!
Will

Will J Dunn

Gmane