Re: Strange behaviour: create database hangs (v 8.4.4)

unregister

On Mon, May 31, 2010 at 9:59 AM, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> Dale Gallagher <dale.gallagher <at> gmail.com> writes:
>> On 31 May 2010 13:10, Dale Gallagher <dale.gallagher <at> gmail.com> wrote:
>>> I am now using strace on postgres startup and watching the filesystem
>>> during the creation of the database. So far, it seems that files in
>>> PGHOME/base/1234/ are appearing, but VERY slowly. Thus far it's taken
>>> 7 minutes and 92 files have been created. The command has not
>>> completed within psql....
>>>
>>> Could this be a filesystem corruption issue? The host is using ext4.
>
>> The command finally completed. It took a VERY long time. Something is
>> wrong, but I have no clue what.
>
> Seems like you must have a kernel/filesystem issue, but I don't know
> what either.  CREATE DATABASE just copies the contents of template1
> using some absolutely straightforward code (look into src/port/copydir.c)
> so it's entirely filesystem-limited.
>
> You could try tracing the backend that's doing the command with
> "strace -r -p <pid>" to try to get an idea of which system calls
> are eating all the time.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
> To make changes to your subscription:

Re: What is field separator?

Re: What is field separator?

Tena Sakai wrote:
>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>  > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> What's denoted as \t is not a tab character.  It is a two character
> sequence backslash followd by a t.  I have tried all other possibilities
> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
> god tab character to appear as field separator. 

Yeah, that's a tough one.  In bash you can use ANSI C quoting to pull 
this off:

psql -c "select name,setting from pg_settings limit 1" -d postgres -At 
-F $'\t'

See http://wiki.bash-hackers.org/syntax/quoting for more information.  
Portability to other shells I'm not sure about.

Note that I did two other things a little differently than your example, 
as well as tightening up the abbreviations:

-Putting the command in -c " " is just better all around than piping 
into psql using echo.  It even works with multi-line input, i.e.:

psql -c "
select 1
"

-Explicitly specifying the database manually using -d makes what's 
happening easier to follow than expecting people to know the convention 
that the first non-option passed to psql is a database name.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg <at> 2ndQuadrant.com   www.2ndQuadrant.us

--

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

Re: What is field separator?

Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:

> But when I do this:
>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>  > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> (here the only change from the previous is the specification of fieldsep)
> Then I get:
>   RS3094315\t0.578121\t0.735

Use a literal tab.  You can enter this in decent shells with Ctrl-V followed by a tab.

-- 
Álvaro Herrera <alvherre <at> commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--

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

Re: What is field separator?

Many thanks, Greg!

I got it to work by emulating what you wrote.
But I think you meant -A, not -At below.
>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>> -F $'\t'

Regards,

Tena Sakai
tsakai <at> gallo.ucsf.edu

On 5/31/10 8:07 PM, "Greg Smith" <greg <at> 2ndquadrant.com> wrote:

> Tena Sakai wrote:
>>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
>> What's denoted as \t is not a tab character.  It is a two character
>> sequence backslash followd by a t.  I have tried all other possibilities
>> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
>> god tab character to appear as field separator.
> 
> Yeah, that's a tough one.  In bash you can use ANSI C quoting to pull
> this off:
> 
> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
> -F $'\t'
> 
> See http://wiki.bash-hackers.org/syntax/quoting for more information.
> Portability to other shells I'm not sure about.
> 
> Note that I did two other things a little differently than your example,
> as well as tightening up the abbreviations:
> 
> -Putting the command in -c " " is just better all around than piping
> into psql using echo.  It even works with multi-line input, i.e.:
> 
> psql -c "
> select 1
> "
> 
> -Explicitly specifying the database manually using -d makes what's
> happening easier to follow than expecting people to know the convention
> that the first non-option passed to psql is a database name.

--

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

Re: What is field separator?

Thank you, Alvaro.

Literal tab, that's one thing I never tried.
Yes, it works.

Regards,

Tena Sakai
tsakai <at> gallo.ucsf.edu

On 5/31/10 8:58 PM, "Alvaro Herrera" <alvherre <at> commandprompt.com> wrote:

> Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:
> 
>> But when I do this:
>>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
>> (here the only change from the previous is the specification of fieldsep)
>> Then I get:
>>   RS3094315\t0.578121\t0.735
> 
> Use a literal tab.  You can enter this in decent shells with Ctrl-V followed
> by a tab.

--

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

Re: Strange behaviour: create database hangs (v 8.4.4)

On 31 May 2010 18:59, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> Seems like you must have a kernel/filesystem issue, but I don't know
> what either.  CREATE DATABASE just copies the contents of template1
> using some absolutely straightforward code (look into src/port/copydir.c)
> so it's entirely filesystem-limited.

Thanks Tom. I've confirmed that it's not related to PostgreSQL, as
installing this version on another system has zero issues. I've posted
the issue to a Slackware specific forum:

http://www.linuxquestions.org/questions/slackware-14/slackware-13-sluggish-system-possibly-filesystem-811442/

--

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

Restore DataBase

Re: What is field separator?

Tena Sakai wrote:
> I got it to work by emulating what you wrote.
> But I think you meant -A, not -At below.
>   
>>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>>> -F $'\t'
>>>       

You had -t in your original to turn off the display of column names and 
the counts at the end, and -A is the shortcut for what you had as "-P 
'format=unaligned'".  I throw "-At" into almost every use of psql from a 
bash script I do, that's the usual combination that gets the basic 
format to be right; then tweak things like the field separator afterwards.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg <at> 2ndQuadrant.com   www.2ndQuadrant.us

--

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

Re: Restore DataBase