Re: [GENERAL] Unrecognized service

[Augori]

Hmm no, I wasn't able to find it.  So I definitely didn't restart it in the
interim.

I'm thinking I must have used the command incorrectly since I didn't really
understand what it was doing. Maybe I invoked it from the wrong place?  But
I was at the root, according to the prompt I pasted, so I don't really
understand it either.

root@server# ps ax | grep post





On Thu, Apr 17, 2014 at 10:31 PM, Adrian Klaver
wrote:

> On 04/17/2014 05:55 PM, Augori wrote:
>
>> Hi Adrian,
>>
>> I don't know the answer to #1. Was that a wildcard search?
>>
>
> It was a grep of the processes running. You replied with this:
>
>
> "Here's what the ps command gives:
>
> root(at)server# ps ax | grep post
>
>  9165 pts/1S+ 0:00 grep post"
>
> If postgres was running it should have shown up above. Just seemed to be
> at odds with later post:
>
>
> "
> woohoo!
>
> service postgresql-9.2 status
>
> (pid  9924) is running...
> "
>
> All I can figure is at some point between the first and second post the
> service for Postgres was started again. There just was no mention of that
> so I was trying to figure out the sequence of events.
>
>
>
>> As for #2, I should have been more clear, that's not a check that it
>> does every night.  I just ran that check when it seemed to be down.  It
>> has been a long time to since I've worked with it, so I didn't correctly
>> recall that I needed to look for postgresql-9.2 and not just postgresql.
>>The problem came up because the nightly process (a Python script that
>> uses psycopg2) tried to, but couldn't connect to postgresql-9.2 server.
>>
>> Does that make more sense?
>>
>
> Sort of, if the Python script is the only thing hitting the database.
> Otherwise I would have expected other 'users' to notice the database was
> down. Furthermore, psycopg2 does not know service names, it connects to a
> port, host, database as a user. So unless those where changed I would
> expect it to keep on connecting, unless the service was stopped, which
> seems to follow what I mention above. At any rate everything worked out.
>
>
>>
>> On Tue, Apr 15, 2014 at 12:08 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 04/14/2014 08:25 PM, Augori wrote:
>>
>> Hi all,
>>
>> woohoo!
>>
>> service postgresql-9.2 status
>>
>> (pid  9924) is running...
>>
>>
>> It seems that I was looking for the service by the wrong
>> name, as
>> John guessed correcty.Also, Tom, it's good to know that the
>> data
>> won't necessarily go away if I need to reinstall at some point.
>>
>>
>> Well that still leaves two questions unanswered.
>>
>> 1) Why did the postgres process not show up in the ps ax output?
>>
>> 2) Why is the nightly process doing a status check on postgresql not
>> postgresql-9.2 ?
>>
>>  >From the original post:
>>
>>
>> # service postgresql status
>> postgresql: unrecognized service
>>
>>
>>
>>
>> thank you so much for the messages from all three of you.  Your
>> rapid
>> responses were very encouraging.
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Return and sql tuple descriptions are incompatible

[Joe Conway]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

(resending to the list as well for the sake of the archives)

On 04/25/2014 08:53 AM, Hengky Liwandouw wrote:
> Thanks to give me the right direction to get help. Okay, here it 
> the detail.
> 
> CREATE table test (id SERIAL, produkid TEXT, warehousename TEXT, 
> onhand INTEGER);
> 
> COPY test (id, produkid, warehousename, onhand) FROM stdin; 1 
> 279140414 OFFICE  10 2279140421   STORE2  10 3279140414 
> STORE245 \.
> 
> select * from crosstab('select produkid, warehousename, onhand
> from test order by 1,2') as ct (row_name text, categori_1 text, 
> categori_2 text)
> 
> Result : ERROR:  return and sql tuple descriptions are 
> incompatible

Great -- thanks.

First of all, I suspect you do not want to use this form (the single
argument form) of the crosstab function. See:

  http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

Specifically:
"The crosstab function produces one output row for each consecutive
group of input rows with the same row_name value. It fills the output
value columns, left to right, with the value fields from these rows.
If there are fewer rows in a group than there are output value
columns, the extra output columns are filled with nulls; if there are
more rows, the extra input rows are skipped."

- - and -

"The main limitation of the single-parameter form of crosstab is that
it treats all values in a group alike, inserting each value into the
first available column. If you want the value columns to correspond to
specific categories of data, and some groups might not have data for
some of the categories, that doesn't work well."

Therefore I would be willing to bet what you really want is something
like:

select *
from crosstab
(
  'select produkid, warehousename, onhand
   from test order by 1,2',
  'select distinct warehousename from test order by 1'
)
as ct (produkid text, office int, store2 int);
   produkid| office | store2
- ---++
 279140414 | 10 | 45
 279140421 || 10
(2 rows)

- - or -

select *
from crosstab
(
  'select warehousename,produkid, onhand
   from test order by 1,2',
  'select distinct produkid from test order by 1'
)
as ct (warehousename text, p_279140414 int, p_279140421 int);
 warehousename | p_279140414 | p_279140421
- ---+-+-
 OFFICE|  10 |
 STORE2|  45 |  10
(2 rows)

The second SQL provides the "category". It is generally useful to run
this from your app first, get a list of the actual categories based on
current data, and then have the app write the crosstab query
dynamically and execute it. In that case the last example might
actually be written as:

select *
from crosstab
(
  'select warehousename,produkid, onhand
   from test order by 1,2',
  'values (279140414),(279140421)'
)
as ct (warehousename text, p_279140414 int, p_279140421 int);
 warehousename | p_279140414 | p_279140421
- ---+-+-
 OFFICE|  10 |
 STORE2|  45 |  10
(2 rows)

HTH,

Joe

- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTWpBQAAoJEDfy90M199hlezsP/0cXv+ES1G68HXG2zONgZNT7
fcF665ZlofNYfKmzAy9KH9OuIeyGVO3tAd2So+xv42hZKca/M4FKVDd9bRqLlRZO
56ecfBGQIHFAQPyHk1pz4cddpHT+3NU5Ub40R/E+VoY6XkdBcaHRGEPMtDNoMm51
qHQpDV9vTsYAc3QZTamnBQ5IyPl1m2QUEeUq4y7IX69I9J/4+D8nprSzJm2ILHVX
RBjIydSZRjL8RabYcHA32HWX4zJJwekdybutgVXws3HBP8YkJcsV/flsqAu6kO0M
24V2lqA/aQbG2ggID3I99CE8MVNWpybV+vLEUUfwMMTPql/4GQF5GdlaZD9kkfp5
vGj9kHQIrW67JyoEAck/Nv0rjlwFAKXFqMi2XNR5MCfXIqJhKmIwk7jXTEE1jkP3
s6VCcvQTTGUtqZyyGq1bqCQ7/d71G4VAthxrYLz/ZajJ8N2HIfbcvB9LbAS4Qig7
i401yCzaGo9SuALKWN7pRZzI6aJZrcBshbvBSqFZQdWNlpbbaDoPwEgQ3MLT+7Xp
NdFF8HokrwNFQ416cUYVfi84s0whSzjHt4iZprUxhYCesvZgDnE8LUlJhz7aeiBD
3Xi+YDE9MqDrQPYNP5wXu2TsNTXQzyol7jo2fpvRbjs6T2B40Jc+SERGr2gUPrpF
bK4ejASGoW7z6uJUJwWd
=WSMX
-END PGP SIGNATURE-


-- 
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] Return and sql tuple descriptions are incompatible

[Hengky Liwandouw]

Joe, 

Thanks to give me the right direction to get help. Okay, here is the detail.


CREATE table test (id SERIAL, produkid TEXT, warehousename TEXT, onhand
INTEGER);

COPY test (id, produkid, warehousename, onhand) FROM stdin;
1   279140414   OFFICE  10
2   279140421   STORE2  10
3   279140414   STORE2  45
\.

select *
from crosstab('select produkid, warehousename, onhand from test order by
1,2') 
as ct (row_name text, categori_1 text, categori_2 text)

Result : ERROR:  return and sql tuple descriptions are incompatible

Hope to get solution for this.

Hengky

-Original Message-
From: Joe Conway [mailto:m...@joeconway.com] 
Sent: Friday, April 25, 2014 10:23 PM
To: Hengky Liwandouw; 'pgsql-general General'
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/25/2014 12:19 AM, Hengky Liwandouw wrote:
> I have read the instruction, change warehouseid to text, spend
> several hours to test many option but it doesn't help.

Please send a complete, self-contained test case which reproduces your
error. By that I mean CREATE statements for tables and view involved,
COPY statements with sufficient sample data, and your failing query. I
realize you have sent bits and pieces of this, but it should be
together in one spot. Then we can easily cut and paste to reproduce
the error, and determine the issue.

In other words, help us help you.

Joe

- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTWm+0AAoJEDfy90M199hlpfQP/2wpUJcAj2Z6+uOAbqJoBYDJ
JY/9oj/dkuySAqL4qWQSLqNZf8nwPNXpst9exQs4Sf6GQa+j47AamWy+G1++J/x8
HRe76VslP1r7vBY4x0kx7a3U8KkcfrPlZAOLVjpFDjvcALPMVgy8AfHRLWTSOmQt
VmjKe1t/k1Znb8buAAUILs+1Q0c81hihv7AGTUZQL/oxCBBmNPpmZPHFR3ZO3NOm
wfmLP/U/lHGd2ev/d9DShi0Q2LKyPQu4Q/WIsc6z+eThd991rP3TIlhKl79snzxv
XgFEQRP2DeRgW/S/43gguIWb53Wimfj0RN6mJZN9DFJO72kAyin0Cs2BDKN3kHbD
D8UkE9Qjnj7UqZzIrZ3+XLLmG04KNmFl9H1HxD8xPudyO4DG4gXkbsvSQ9US0ThG
ZOk5ipOmUupjlgEis9W1QvDlc8jqJo5AbYLPAQ7S9cd9ougKDsD7YUeQdSwQod+H
ls2u2JqFfdewsb5YupSGlvY6k5SVchz+SD8JSlYXRG4yqiLivHwudwsSADaQAJyj
YZiLduHoXKjnPKASpqATTtfSvXdKxbJLJZg9Gf5CgLIlIl30tQjMRQ/iFfPq2jTD
rK8p+AKQrVoHD8/7T1rkBfLa9Vp3r/BqyYz3OgSLcHhnZg5lanfSK0jwKKNSGIoV
2sSy4zVI9+ioNL+lUggN
=JHtf
-END PGP SIGNATURE-



-- 
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] Return and sql tuple descriptions are incompatible

[Joe Conway]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/25/2014 12:19 AM, Hengky Liwandouw wrote:
> I have read the instruction, change warehouseid to text, spend
> several hours to test many option but it doesn't help.

Please send a complete, self-contained test case which reproduces your
error. By that I mean CREATE statements for tables and view involved,
COPY statements with sufficient sample data, and your failing query. I
realize you have sent bits and pieces of this, but it should be
together in one spot. Then we can easily cut and paste to reproduce
the error, and determine the issue.

In other words, help us help you.

Joe

- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTWm+0AAoJEDfy90M199hlpfQP/2wpUJcAj2Z6+uOAbqJoBYDJ
JY/9oj/dkuySAqL4qWQSLqNZf8nwPNXpst9exQs4Sf6GQa+j47AamWy+G1++J/x8
HRe76VslP1r7vBY4x0kx7a3U8KkcfrPlZAOLVjpFDjvcALPMVgy8AfHRLWTSOmQt
VmjKe1t/k1Znb8buAAUILs+1Q0c81hihv7AGTUZQL/oxCBBmNPpmZPHFR3ZO3NOm
wfmLP/U/lHGd2ev/d9DShi0Q2LKyPQu4Q/WIsc6z+eThd991rP3TIlhKl79snzxv
XgFEQRP2DeRgW/S/43gguIWb53Wimfj0RN6mJZN9DFJO72kAyin0Cs2BDKN3kHbD
D8UkE9Qjnj7UqZzIrZ3+XLLmG04KNmFl9H1HxD8xPudyO4DG4gXkbsvSQ9US0ThG
ZOk5ipOmUupjlgEis9W1QvDlc8jqJo5AbYLPAQ7S9cd9ougKDsD7YUeQdSwQod+H
ls2u2JqFfdewsb5YupSGlvY6k5SVchz+SD8JSlYXRG4yqiLivHwudwsSADaQAJyj
YZiLduHoXKjnPKASpqATTtfSvXdKxbJLJZg9Gf5CgLIlIl30tQjMRQ/iFfPq2jTD
rK8p+AKQrVoHD8/7T1rkBfLa9Vp3r/BqyYz3OgSLcHhnZg5lanfSK0jwKKNSGIoV
2sSy4zVI9+ioNL+lUggN
=JHtf
-END PGP SIGNATURE-


-- 
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] unexpected data offset flag 0

[Tom Lane]

matthias.trauf...@bd.zh.ch writes:
> I'm trying to restore a database dumpfile I made of a small 9.3.4 database 
> using pg_dump -Fc -v -f "db.dump" db, but when I try to restore it on the 
> same or on another 9.3.4 system with  pg_restore -C -d postgres db.dump I 
> get the following error message:
> pg_restore: [archiver] unexpected data offset flag 0
> Even listing the contents doesn't work: pg_restore -l db.dump
> pg_restore: [archiver] unexpected data offset flag 0
> I couldn't find anything in the documentation about this error message. Is 
> it possible that the dump is corrupted?

Yeah, sounds that way :-(

> What can I do to fix this?

Probably not much, so far as this copy of the file is concerned.  My first
guess as to what happened to it is Windows to non-Windows (or vice versa)
newline conversion, which could add or remove bytes and thus mangle the
file's archive structure.  How did you transfer the file across machines
exactly?

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] How to find out PIDs of transactions older than the current?

[Torsten Förtsch]

On 25/04/14 13:26, Torsten Förtsch wrote:
> I think I can find out the transaction ids of concurrent transactions
> older than the current one by:
> 
>   select * from txid_snapshot_xip(txid_current_snapshot())
>   union
>   select * from txid_snapshot_xmax(txid_current_snapshot());
> 
> Now, I want to map these transaction ids to backend process ids.
> pg_stat_activity does not provide the transaction id. So, I turned to
> pg_locks.
> 
> select l.pid
>   from (
>   select * from txid_snapshot_xip(txid_current_snapshot())
>   union
>   select * from txid_snapshot_xmax(txid_current_snapshot())) tx(id)
>   join pg_locks l
> on (l.locktype='transactionid'
> and l.transactionid::TEXT::BIGINT=tx.id);
> 
> This works. But my transaction ids are still far less than 2^32.

I think I got it. pg_locks.transactionid is a 4-byte quantity. But
I can safely ignore the upper half of the BIGINT that comes out of
txid_snapshot_xip(). So, the query becomes:

select l.pid
  from (
  select * from txid_snapshot_xip(txid_current_snapshot())
  union
  select * from txid_snapshot_xmax(txid_current_snapshot())) tx(id)
  join pg_locks l
on (l.locktype='transactionid'
and l.transactionid::TEXT::BIGINT=tx.id & (1::BIGINT<<32)-1)

Torsten


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

[GENERAL] How to find out PIDs of transactions older than the current?

[Torsten Förtsch]

Hi,

I think I can find out the transaction ids of concurrent transactions
older than the current one by:

  select * from txid_snapshot_xip(txid_current_snapshot())
  union
  select * from txid_snapshot_xmax(txid_current_snapshot());

Now, I want to map these transaction ids to backend process ids.
pg_stat_activity does not provide the transaction id. So, I turned to
pg_locks.

select l.pid
  from (
  select * from txid_snapshot_xip(txid_current_snapshot())
  union
  select * from txid_snapshot_xmax(txid_current_snapshot())) tx(id)
  join pg_locks l
on (l.locktype='transactionid'
and l.transactionid::TEXT::BIGINT=tx.id);

This works. But my transaction ids are still far less than 2^32.

Will it also work after the wraparound? I am worried because there is no
default cast from XID to INT or BIGINT.

Is there a better way?

Thanks,
Torsten


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

[GENERAL] unexpected data offset flag 0

[matthias . trauffer]

Hello,

I'm trying to restore a database dumpfile I made of a small 9.3.4 database 
using pg_dump -Fc -v -f "db.dump" db, but when I try to restore it on the 
same or on another 9.3.4 system with  pg_restore -C -d postgres db.dump I 
get the following error message:
pg_restore: [archiver] unexpected data offset flag 0
Even listing the contents doesn't work: pg_restore -l db.dump
pg_restore: [archiver] unexpected data offset flag 0
I couldn't find anything in the documentation about this error message. Is 
it possible that the dump is corrupted? What can I do to fix this?
 
Matthias

Re: [GENERAL] Return and sql tuple descriptions are incompatible

[Hengky Liwandouw]

Hi, 

I have read the instruction, change warehouseid to text, spend several hours
to test many option but it doesn't help. 

This is the new query :

SELECT *
FROM   crosstab(
  'SELECT produkid, warehousename, onhand
   FROM   vwtest
   ORDER  BY 1,2')  
AS ct ("Produkid" text, "Store1" text, "Store2" text, "Store3" text);


Vwtest itself is a view with 3 column output: produkid(text),
warehousename(text), onhand(integer)

CREATE OR REPLACE VIEW public.vwtest (
produkid,
warehousename,
onhand)
AS
SELECT dt.produkid::text AS produkid,
tblwarehouse.warehousename::text AS warehousename,
sum(dt.awal + dt.ttlmsk - dt.ttlklr)::integer AS onhand
FROM (
SELECT tblstockawal.kodebarang AS produkid,
tblstockawal.warehouseid,
sum(tblstockawal.qty) AS awal,
0 AS ttlmsk,
0 AS ttlklr
FROM tblstockawal
GROUP BY tblstockawal.kodebarang, tblstockawal.warehouseid
UNION
SELECT tbltransaksi.kodebarang AS produkid,
tbltransaksi.warehouseid,
0 AS awal,
sum(tbltransaksi.masuk) AS ttlmsk,
sum(tbltransaksi.keluar) AS ttlklr
FROM tbltransaksi
GROUP BY tbltransaksi.kodebarang, tbltransaksi.warehouseid
) dt
   JOIN tblwarehouse ON tblwarehouse.id = dt.warehouseid
GROUP BY dt.produkid, tblwarehouse.warehousename;

The error still the same.

I really have no idea 


-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston
Sent: Friday, April 25, 2014 2:20 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

Hengky Lie wrote
> Select * from crosstab($$select produkid, warehouseid,onhand from vwtest
> order by 1,2$$)
> 
> as t (produkid VARCHAR, warehouseid integer)
> 
> The crosstab command didn't work with error : Return and sql tuple
> descriptions are incompatible.
> 
> I have tried to change productid type to text and warehouseid to float8
> and
> the problem still not solve.
> 
> What is wrong with the command ?

Go read: http://www.postgresql.org/docs/9.3/interactive/tablefunc.html
"F.36.1.2. crosstab(text)" again and then, looking at the data you are
passing to the crosstab function, see if you can determine the correct
number of columns that need to be declared in the "as t (...)" section.  The
answer is likely not "2" since one of those is a row name and if you only
have a single warehouse a crosstab seems pointless...

Note that even if "warehouseid" is an integer it is going to be effectively
converted to text since the values of the categories all become column
labels...

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Return-and-sql-tuple-descriptions-ar
e-incompatible-tp5801414p5801450.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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



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