Re: puzzling perl DBI vs psql problem

Yes, I am running with use strict. The statement I pasted in is after perl
quoting, being written out by the same perl program. I just take that
statement and paste it into the psql window.

DBI->trace showed nothing out of the ordinary. It just shows the lines
being fetched that I am seeing in the web program, not the lines I get from
psql.

Another odd thing is that it is apparently not logging statements from
Perl, only from psql. I don't know why. I thought I had it set up right
to log to syslog. I've had good luck before with that on other
installations.

Here are the log settings in postgresql.conf:

log_destination = 'syslog' # Valid values are combinations of
# stderr, csvlog, syslog, and
eventlog,
# depending on platform. csvlog
# requires logging_collector to be
on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and
csvlog
#logging_collector = off # Enable capturing of
stderr and csvlog
# into log files. Required to be on
for
# csvlogs.
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are
written,
log_filename = 'postgresql-%a.log' # log file name pattern,
#log_file_mode = 0600 # creation mode for log files,
log_truncate_on_rotation = on # If on, an existing log file with
the
# same name as the new log file
will be
log_rotation_age = 1d # Automatic rotation of logfiles
will
log_rotation_size = 0 # Automatic rotation of logfiles
will
# happen after that much log output.
# These are relevant when logging to syslog:
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
# This is only relevant when logging to eventlog (win32):
client_min_messages = log # values in order of decreasing
detail:
# log
log_min_messages = info # values in order of decreasing detail:
#log_min_messages = warning # values in order of decreasing
detail:
# log
#log_min_error_statement = error # values in order of decreasing
detail:
# log
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all
statements
# and their durations, > 0 logs only
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose
messages
#log_hostname = off
log_line_prefix = '%d %u %p %t ' # special values:

Could it have something to do with permissions on /var/log/postgresql? It
is writeable by root only. The perl program runs under apache.

Susan

On Thu, Mar 13, 2014 at 12:46 PM, Steve Atkins <steve(at)blighty(dot)com> wrote:

>
> On Mar 13, 2014, at 12:18 PM, Susan Cassidy <
> susan(dot)cassidy(at)decisionsciencescorp(dot)com> wrote:
>
> > I have a weird issue that I can't figure out.
> >
> > If I run the exact same query through psql as through perl DBI, I get
> different results. I get far fewer results with DBI than through the psql
> command line.
> >
> > Any ideas why that would be?
> >
> > The query is:
> > SELECT st.description, st.scene_thing_instance_id,
> > st.scene_id, sc.description, st.scene_thing_id, s.description,
> > st.position_x, st.position_y, st.position_z,
> > CASE
> > when (st.description = 'absolute root'::text) then 1
> > when (st.description ilike 'root%') then 2
> > else 3
> > END as s1, s.shape_name_id, sn.shape_name
> > from scene_thing_instances st
> > left join scene_things s on st.scene_thing_id = s.scene_thing_id
> > left join scenes sc on st.scene_id = sc.scene_id
> > left outer join shape_names sn on s.shape_name_id =
> sn.shape_name_id
> >
> > order by s1, st.description
> >
> > I get 14 rows back via psql, but I only get 5 rows back via DBI. It's
> very puzzling.
> >
> > I copied and pasted the query from the program's log file, so I know I'm
> doing the exact same query. If it matters, I'm only seeing the rows with
> 'root' in them via DBI, which the CASE statement refers to.
>
> How are you quoting the string in perl, and are you running with use
> strict?
>
> My first thought would be that you're not running the query you think you
> are - logging it at the postgresql side will let you check that (or if
> that's not possible, DBI's trace methods can help).
>
> Cheers,
> Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>