[GENERAL] [ask] Return Query

[ataherster]

hai all, i'm trying create function like this

CREATE OR REPLACE FUNCTION penjualan(idcb integer)
 RETURNS SETOF penjualan AS
$BODY$
BEGIN

IF ($1 IS NULL) THEN
 return query SELECT * FROM PENJUALAN;
ELSE
 return query SELECT * FROM PENJUALAN WHERE IDCABANG=$1;
END IF;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100
 ROWS 1000;

but this function is not work with this error : 
ERROR:  structure of query does not match function result type

CONTEXT:  PL/pgSQL function "penjualan" line 6 at RETURN QUERY

on the time i try with other table and working well

thanks for your help



--
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] ODBC limitation??

[Martin Gainty]

I would see if you could trim down the statement and keep the statement all on 
one 
line (if possible) 
if you cant trim it down try putting the logic in a function

HTH
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 




> From: akla...@comcast.net
> To: pgsql-general@postgresql.org; carl.sopc...@cegis123.com
> Subject: Re: [GENERAL] ODBC limitation??
> Date: Sat, 14 Mar 2009 18:36:51 -0700
> 
> On Saturday 14 March 2009 5:40:40 pm Carl Sopchak wrote:
> > When I run the following query through psql, it executes successfully.
> > However, when I run it through ODBC (via OpenOffice Base), I get the error
> >
> > SQL Status: HY000
> > Error code: 1000
> >
> > syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
> >
> > Is this some limitation of ODBC?  (I don't think so, so I'm going to ask on
> > the OpenOffice lists, but thought I'd check here, too...)
> >
> > Query:
> >
> > select number_of_years,
> > max(case when trial_id = 1 then period_results else null end) as
> > MaxResults1, min(case when trial_id = 1 then period_results else null end)
> > as MaxResults1, max(case when trial_id = 2 then period_results else null
> > end) as MaxResults2, min(case when trial_id = 2 then period_results else
> > null end) as MaxResults2 from trial_results
> > where trial_id in (1,2)
> > group by number_of_years
> > order by number_of_years;
> >
> >
> > This is on Linux, Fedora 8, using PostgreSQL 8.3.6 and unixODBC...
> >
> > Thanks for the help,
> >
> > Carl
> 
> This is an OO problem, at some point OO Base translates ODBC and JDBC queries 
> into its native SDBC format and it has some parser limitations. To get this 
> to 
> run you will have to turn of the query builder and just run it as a pass 
> through query.
> 
> -- 
> Adrian Klaver
> akla...@comcast.net
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_
Hotmail® is up to 70% faster. Now good news travels really fast. 
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009

Re: [GENERAL] ODBC limitation??

[Adrian Klaver]

On Saturday 14 March 2009 5:40:40 pm Carl Sopchak wrote:
> When I run the following query through psql, it executes successfully.
> However, when I run it through ODBC (via OpenOffice Base), I get the error
>
> SQL Status: HY000
> Error code: 1000
>
> syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
>
> Is this some limitation of ODBC?  (I don't think so, so I'm going to ask on
> the OpenOffice lists, but thought I'd check here, too...)
>
> Query:
>
> select number_of_years,
> max(case when trial_id = 1 then period_results else null end) as
> MaxResults1, min(case when trial_id = 1 then period_results else null end)
> as MaxResults1, max(case when trial_id = 2 then period_results else null
> end) as MaxResults2, min(case when trial_id = 2 then period_results else
> null end) as MaxResults2 from trial_results
> where trial_id in (1,2)
> group by number_of_years
> order by number_of_years;
>
>
> This is on Linux, Fedora 8, using PostgreSQL 8.3.6 and unixODBC...
>
> Thanks for the help,
>
> Carl

This is an OO problem, at some point OO Base translates ODBC and JDBC queries 
into its native SDBC format and it has some parser limitations. To get this to 
run you will have to turn of the query builder and just run it as a pass 
through query.

-- 
Adrian Klaver
akla...@comcast.net

-- 
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] Maximum transaction rate

[Joshua D. Drake]

On Sun, 2009-03-15 at 01:48 +0100, Marco Colombo wrote:
> Joshua D. Drake wrote:
> > On Sat, 2009-03-14 at 05:25 +0100, Marco Colombo wrote:
> >> Scott Marlowe wrote:
> > 
> >> Also see:
> >> http://lkml.org/lkml/2008/2/26/41
> >> but it seems to me that all this discussion is under the assuption that
> >> disks have write-back caches.
> >> "The alternative is to disable the disk write cache." says it all.
> > 
> > If this applies to raid based cache as well then performance is going to
> > completely tank. For users of Linux + PostgreSQL using LVM.
> > 
> > Joshua D. Drake
> 
> Yet that's not the point. The point is safety. I may have a lightly loaded
> database, with low write rate, but still I want it to be reliable. I just
> want to know if disabling the caches makes it reliable or not.

I understand but disabling cache is not an option for anyone I know. So
I need to know the other :)

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
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] ODBC limitation??

[Dann Corbit]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Carl Sopchak
> Sent: Saturday, March 14, 2009 5:41 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] ODBC limitation??
> 
> When I run the following query through psql, it executes successfully.
> However, when I run it through ODBC (via OpenOffice Base), I get the
> error
> 
> SQL Status: HY000
> Error code: 1000
> 
> syntax error, unexpected $end, expecting BETWEEN or IN or
> SQL_TOKEN_LIKE
> 
> Is this some limitation of ODBC?  (I don't think so, so I'm going to
> ask on
> the OpenOffice lists, but thought I'd check here, too...)
> 
> Query:
> 
> select number_of_years,
> max(case when trial_id = 1 then period_results else null end) as
> MaxResults1,
> min(case when trial_id = 1 then period_results else null end) as
> MaxResults1,
> max(case when trial_id = 2 then period_results else null end) as
> MaxResults2,
> min(case when trial_id = 2 then period_results else null end) as
> MaxResults2
> from trial_results
> where trial_id in (1,2)
> group by number_of_years
> order by number_of_years;
> 
> 
> This is on Linux, Fedora 8, using PostgreSQL 8.3.6 and unixODBC...

I am not speaking with specific knowledge about the official PostgreSQL
ODBC driver, but support for the above grammar is not demanded by the
actual ODBC specification.  Many ODBC drivers have a pass-through mode.
You might check the documentation for the official driver and see if it
has one.  Any query that will work from PSQL will work in pass-through
mode.

-- 
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] Maximum transaction rate

[Marco Colombo]

Joshua D. Drake wrote:
> On Sat, 2009-03-14 at 05:25 +0100, Marco Colombo wrote:
>> Scott Marlowe wrote:
> 
>> Also see:
>> http://lkml.org/lkml/2008/2/26/41
>> but it seems to me that all this discussion is under the assuption that
>> disks have write-back caches.
>> "The alternative is to disable the disk write cache." says it all.
> 
> If this applies to raid based cache as well then performance is going to
> completely tank. For users of Linux + PostgreSQL using LVM.
> 
> Joshua D. Drake

Yet that's not the point. The point is safety. I may have a lightly loaded
database, with low write rate, but still I want it to be reliable. I just
want to know if disabling the caches makes it reliable or not. People on LK
seem to think it does. And it seems to me they may have a point.
fsync() is a flush operation on the block device, not a write barrier. LVM
doesn't pass write barriers down, but that doesn't mean it doesn't perform
a flush when requested to.

.TM.


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

[GENERAL] ODBC limitation??

[Carl Sopchak]

When I run the following query through psql, it executes successfully.  
However, when I run it through ODBC (via OpenOffice Base), I get the error 

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Is this some limitation of ODBC?  (I don't think so, so I'm going to ask on 
the OpenOffice lists, but thought I'd check here, too...)

Query:

select number_of_years, 
max(case when trial_id = 1 then period_results else null end) as MaxResults1,
min(case when trial_id = 1 then period_results else null end) as MaxResults1,
max(case when trial_id = 2 then period_results else null end) as MaxResults2,
min(case when trial_id = 2 then period_results else null end) as MaxResults2
from trial_results 
where trial_id in (1,2) 
group by number_of_years 
order by number_of_years;


This is on Linux, Fedora 8, using PostgreSQL 8.3.6 and unixODBC...

Thanks for the help,

Carl

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

[GENERAL] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart

[Christophe]

Hi,

The video is now available for download!  You can find it at:

http://blog.thebuild.com/sfpug/sfpug-unison-20090311.mov

Thanks,
-- Christophe

--
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] Streaming PUG meeting: PostgreSQL Genetics!

[Nathan Boley]

>> Will there be a saved version of this (SFPUG talk) available for later 
>> viewing? Don't
>> make me choose between steak and beer and postgres. 8(
>
> Yes!  I'll announce it here when it's available.
>

Is there any update on this?

-Nathan

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

[GENERAL] Comemoracao Niver PH - 20, 21 e 22/03!

[baptista]

Queridos amigos e amigas,

Dia 21/3, farei 32 anos e comemorarei em 3 ocasiões!

Na 6a feira (20/3), espero vocês no Happy Hour da Gafieira Lapa 40 Graus (Rua
do Riachuelo, 97) no 1º andar, estarei lá a partir das 19h. A casa conta com
mesas de sinuca e shows ao vivo. A entrada é de apenas R$ 5,00 até as 21:00 para homens e mulheres!
http://www.lapa40graus.com.br

No sábado (21/3), a festa continua! Comemorarei no Belmonte na Lapa a partir
das 19:30 e depois vamos todos no show do Frejat e Nando Reis na Fundição
Progresso. A 1/2 entrada (com 1 kg de alimento) está R$ 30,00.
http://www.fundicaoprogresso.com.br/

No domingo (22/3), pra quem tiver folego, comerei uma pizza no rodízio no
Viena do Shopping Tijuca a partir das 18h.
http://www.viena.com.br/

Beijos pra elas e abraços pra eles, PH (9267-2928)

Re: [GENERAL] again...

[Gurjeet Singh]

Hi All,

Acting on a customer's report I analyzed this bug, and have found a fix
for it. It is not a critical error, but it definitely is a bug, and can have
security implications.

This error is raised from syslogger.c, and since this sub-process is not
responsible for any backend communication, the backend (or the non-backend)
that raised this error has already successfully done its work of
communicating the message back to the application, if any.

The security implication is that, that the actual error which was
supposed to be also logged in the server log file, is lost; and instead we
have this error message in the WindowsErrorLog/Syslog.

Issue:


On Windows, the write to log file is done by a thread (whose main
function is pipeThread() ), and since it works completely independent of the
SysLoggerMain() ( which is responsible for calling logfile_rotate()
periodically, which in turn changes the global variable syslogFile) this is
causing a race condition due to an error in the way we are using the related
critical section.

Here's the flow in my opinion which is causing this error:

main => SysLoggerMain() and thread => pipeThread()

main  : calls logfile_rotate()
thread: calls process_pipe_input()/flush_pipe_input()
  -> calls write_syslogger_file()
thread: reads in the current value of global variable
  syslogFile.
main  : enters critical section sysfileSection, and
  assigns a new value to the global variable
  syslogFile
thread: blocked by the same critical section
main  : leaves critical section
thread: enters the critical section and attempts to
 write to the old value of syslogFile.
OS: throws an error, as that handle is already
  closed by main

The solution is to read the global variable inside the critical section, in
write_syslogger_file().

How to reproduce:
=

Apply the syslogger_race_bug_reproduce.patch patch.

Set the following GUCs in postgresql.conf:

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 1kB
log_min_messages = notice

Restart the DB for these changes to take effect.

Create the following plpgsql function:

create or replace function raise_notices() returns void as $$
begin
loop
raise notice 'dummy';
end loop;
end;
$$ language plpgsql;

Execute: select raise_notices();

Keep an eye on Windows error log (refresh often;
screenshot
).

Attachments:
=

syslogger_race_bug_reproduce.patch :
To reproduce the bug.

syslogger_race_bug_reproduce_temp_fix.patch :
To see the effect of final patch, but with other support code.

syslogger_race_bugfix.patch :
Final patch, ready for application to HEAD.

Best regards,

On Sat, Oct 25, 2008 at 8:42 PM, Ati Rosselet wrote:

> I'm still getting a lot of these entries in my eventlog whenever I have a
> reasonably large amount of logging:
>
> Event Type:Error
> Event Source:PostgreSQL
> Event Category:None
> Event ID:0
> Date:10/22/2008
> Time:9:36:28 AM
> User:N/A
> Computer:--
> Description:
> could not write to log file: Bad file descriptor
>
> production server 8.3.3 (tried with 8.3.4 - same thing), win 2003 server.
> I'd send this to psql-bugs, but I can't figure out how to reproduce it.. and
>
> apparently noone else has seen this?   Or is noone else running postgres on
> win2003??? (survey says??)
>
> The timing of each appears to be when log is rolled over (size limited to
> 10MB) and postgres attempts to write a log entry at the same time:
>
> Any ideas? help? How to proceed???
> Cheers
> Ati
>
> My log settings from postgresql.conf:
>
>
> #--
> # ERROR REPORTING AND LOGGING
>
> #--
> # - Where to Log -
> log_destination = 'stderr'# Valid values are combinations of
>  # This is used when logging to stderr:
> logging_collector = on# Enable capturing of stderr and csvlog
>
> # These are only used if logging_collector is on:
> #log_directory = 'pg_log'# directory where log files are written,
> # can be absolute or relative to PGDATA
> #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'# log file name
> pattern,
> # can include strftime() escapes
> #log_truncate_on_rotation = off# If on, an existing log file of the
> # same name as the new log file will be
> # truncated rather than appended to.
> # But such truncation only occurs on
> # time-driven rotation, not on restarts
> # or size-driven rotation.  Default is
> # off, meaning append to ex

Re: [GENERAL] Maximum transaction rate

[Joshua D. Drake]

On Sat, 2009-03-14 at 05:25 +0100, Marco Colombo wrote:
> Scott Marlowe wrote:

> Also see:
> http://lkml.org/lkml/2008/2/26/41
> but it seems to me that all this discussion is under the assuption that
> disks have write-back caches.
> "The alternative is to disable the disk write cache." says it all.

If this applies to raid based cache as well then performance is going to
completely tank. For users of Linux + PostgreSQL using LVM.

Joshua D. Drake

> 
> .TM.
> 
-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


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

[GENERAL] New shapshot RPMs (Mar 10, 2009) are ready for testing

[Devrim GÜNDÜZ]

Hi,

As we are moving very close to 8.4 beta, please join us for testing 8.4
release.

I just released new RPM sets, which is based on Mar 10 CVS snapshot.
Please note that these packages are **not** production ready. They are
for Fedora 9 and RHEL/CentOS 5. I have no intention to push 8.4
development packages for older Fedora/RHEL/CentOS releases, and also
currently I do not provide (Open)SuSE packages.

These packages *do* require a dump/reload, even from previous 8.4
packages, because of a catversion update.

We have more than 500 testers using these sets. We need more people to
discover any bugs in 8.4 development version.

As usual, please find detailed info from:

http://yum.pgsqlrpms.org

A mini howto about 8.4devel release + RPMs is here:

http://yum.pgsqlrpms.org/news-8.4devel-ready-for-testing.php

The tarball I used is here:

http://yum.pgsqlrpms.org/srpms/8.4

(I will remove tarballs after a few weeks...)

Please report any packaging related errors to me. If you find any
PostgreSQL 8.4 bugs, please post them to pgsql-b...@postgresql.org or
fill out this form: 

http://www.postgresql.org/support/submitbug 

Sincerely,
-- 
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
   http://www.gunduz.org


signature.asc
Description: This is a digitally signed message part