node.js pg query returns fewer rows than native postgresql client

Question

I have an SQL query that returns 356 rows when run in a native Postgresql client (psql, Navicat, etc.), but only returns 214 rows when run inside the node.js service I'm developing. Here's the query:

SELECT discs.id AS id, 
    discs.is_streamable AS is_streamable, 
    discs.updated_at AS updated_at, 
    albums.title AS album_title, 
    'https://www.slurpie.com/albums/' || albums.slug AS album_url, 
    artists.name AS main_artist, 
    genres.name AS genre, 
    albums.cover_remote_url AS album_art 
FROM discs 
JOIN albums 
ON albums.id = discs.album_id 
JOIN artists 
ON artists.id = albums.main_artist_id 
JOIN genres 
ON genres.id = albums.genre_id 
JOIN users 
ON users.id = discs.user_id 
WHERE users.authentication_token = 'itsasecret' 
ORDER BY main_artist

The node.js service is using restify and pg-query (although I've tested it with the underlying "pg" module as well with the same results).

Looking at the output from the query, I can't find any similarities between the rows that are left out when the query is run inside of node (I thought perhaps a null value in a column, or an extremely large amount of column data, special characters, etc.).

Are you logging in with the same user between psql and node? Is it possible that there's some search_path magic happening, and that two or more tables with the same name exist in different schemas? — yieldsfalsehood
– yieldsfalsehood, Commented Dec 4, 2013 at 19:52
Same credentials as the sql client are used in the connection string from the node application. — jasongullickson
– jasongullickson, Commented Dec 4, 2013 at 20:04
Maybe try "explain analyze verbose select ..." through both psql and node and follow through the plan to see where the numbers are differing? — yieldsfalsehood
– yieldsfalsehood, Commented Dec 4, 2013 at 20:10

jasongullickson · Accepted Answer · 2013-12-06 14:13:16Z

2

Yieldsfalsehood was on the right track.

Turns out that the node code was pointed at a recent copy of the database, similar enough that it wasn't obvious that it was a separate database, but out-of-date enough that the number of rows was different.

It wasn't until I noticed a small difference in one of the rows that was returned by both psql and the node app that the cause jumped out at me.

Occams Razor FTW! :)

answered Dec 6, 2013 at 14:13

jasongullickson

1,0511 gold badge14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

node.js pg query returns fewer rows than native postgresql client

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related