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: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.
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@xxxxxxxxxxx> wrote:
On Mar 13, 2014, at 12:18 PM, Susan Cassidy <susan.cassidy@xxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
